Using SQL constraints.SQL constraints are relevant to transactions because they can conceivably prevent you from doing what you want. For example, suppose that you want to add data to a table that contains a column with a NOT NULL constraint.
One common method of adding a record is to append a blank row to your table and then insert values into it later. The NOT NULL constraint on one column, however, causes the append operation to fail. SQL doesn't allow you to add a row that has a null value in a column with a NOT NULL constraint, even though you plan to add data to that column before your transaction ends. To address this problem, SQL enables you to designate constraints as either DEFERRABLE or NOT DEFERRABLE.
Constraints that are NOT DEFERRABLE are applied immediately. You can set DEFERRABLE constraints to be either initially DEFERRED or IMMEDIATE. If a DEFERRABLE constraint is set to IMMEDIATE, it acts like a NOT DEFERRABLE constraint — it is applied immediately. If a DEFERRABLE constraint is set to DEFERRED, it is not enforced. (No, your code doesn't have an attitude problem; it’s simply following orders.)
To append blank records or perform other operations that may violate DEFERRABLE constraints, you can use a statement similar to the following:
SET CONSTRAINTS ALL DEFERRED ;This statement puts all
DEFERRABLE constraints in the DEFERRED condition. It does not affect the NOT DEFERRABLE constraints. After you've performed all operations that could violate your constraints — and the table reaches a state that doesn’t violate them — you can reapply them. The statement that reapplies your constraints looks like this:
SET CONSTRAINTS ALL IMMEDIATE ;If you made a mistake and any of your constraints are still being violated, you find out as soon as this statement takes effect.
If you do not explicitly set your DEFERRED constraints to IMMEDIATE, SQL does it for you when you attempt to COMMIT your transaction. If a violation is still present at that time, the transaction does not COMMIT; instead, SQL gives you an error message.
SQL's handling of constraints protects you from entering invalid data (or an invalid absence of data, which is just as important), at the same time giving you the flexibility to violate constraints temporarily while a transaction is still active.
Consider a payroll example to see why being able to defer the application of SQL constraints is important.
Assume that an EMPLOYEE table has columns EmpNo, EmpName, DeptNo, and Salary. EMPLOYEE.DeptNo is a foreign key that references the DEPT table. Assume also that the DEPT table has columns DeptNo and DeptName. DeptNo is the primary key.
In addition, you want to have a table like DEPT that also contains a Payroll column which (in turn) holds the sum of the Salary values for employees in each department.
Assuming you are using a DBMS that supports this SQL standard functionality, you can create the equivalent of this table with the following view:
CREATE VIEW DEPT2 AS SELECT D.*, SUM(E.Salary) AS Payroll FROM DEPT D, EMPLOYEE E WHERE D.DeptNo = E.DeptNo GROUP BY D.DeptNo ;You can also define this same view as follows:
CREATE VIEW DEPT3 AS
SELECT D.*,
(SELECT SUM(E.Salary)
FROM EMPLOYEE E
WHERE D.DeptNo = E.DeptNo) AS Payroll
FROM DEPT D ;
But suppose that, for efficiency, you don't want to calculate the SUM every time you reference DEPT3.Payroll. Instead, you want to store an actual Payroll column in the DEPT table. You will then update that column every time you change a Salary.To make sure that the Salary column is accurate, you can include a CONSTRAINT in the table definition:
CREATE TABLE DEPT
(DeptNo CHAR(5),
DeptNameCHAR(20),
Payroll DECIMAL(15,2),
CHECK (Payroll = (SELECT SUM(Salary)
FROM EMPLOYEE E
WHERE E.DeptNo= DEPT.DeptNo)));
Now, suppose you want to increase the Salary of employee 123 by 100. You can do it with the following update:
UPDATE EMPLOYEE SET Salary = Salary + 100 WHERE EmpNo = '123' ;With this approach, you must remember to do the following as well:
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo = '123') ;
(You use the subquery to reference the DeptNo of employee 123.)But there's a problem: Constraints are checked after each statement. In principle, all constraints are checked. In practice, implementations check only the constraints that reference the values modified by the statement.
After the first preceding UPDATE statement, the implementation checks all constraints that reference any values that the statement modifies. This includes the constraint defined in the DEPT table, because that constraint references the Salary column of the EMPLOYEE table and the UPDATE statement is modifying that column. After the first UPDATE statement, that constraint is violated.
You assume that before you execute the UPDATE statement the database is correct, and each Payroll value in the DEPT table equals the sum of the Salary values in the corresponding columns of the EMPLOYEE table. When the first UPDATE statement increases a Salary value, this equality is no longer true. The second UPDATE statement corrects this — and again leaves the database values in a state for which the constraint is True. Between the two updates, the constraint is False.
The SET CONSTRAINTS DEFERRED statement lets you temporarily disable or suspend all constraints, or only specified constraints. The constraints are deferred until either you execute a SET CONSTRAINTS IMMEDIATE statement or you execute a COMMIT or ROLLBACK statement. So you surround the previous two UPDATE statements with SET CONSTRAINTS statements. The code looks like this:
SET CONSTRAINTS DEFERRED ;
UPDATE EMPLOYEE
SET Salary = Salary + 100
WHERE EmpNo = '123' ;
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo = '123') ;
SET CONSTRAINTS IMMEDIATE ;
This procedure defers all constraints. If you insert new rows into DEPT, the primary keys won't be checked; you’ve removed protection that you may want to keep. Instead, you should specify the constraints that you want to defer. To do this, name the constraints when you create them:
CREATE TABLE DEPT (DeptNo CHAR(5), DeptName CHAR(20), Payroll DECIMAL(15,2), CONSTRAINT PayEqSumsal CHECK (Payroll = SELECT SUM(Salary) FROM EMPLOYEE E WHERE E.DeptNo = DEPT.DeptNo)) ;With constraint names in place, you can then reference your constraints individually:
SET CONSTRAINTS PayEqSumsal DEFERRED;
UPDATE EMPLOYEE
SET Salary = Salary + 100
WHERE EmpNo = '123' ;
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo = '123') ;
Without a constraint name in the CREATE statement, SQL generates one implicitly. That implicit name is in the schema information (catalog) tables. But specifying the names explicitly is more straightforward.Now suppose that you mistakenly specified an increment value of 1000 in the second UPDATE statement. This value is allowed in the UPDATE statement because the constraint has been deferred. But when you execute SET CONSTRAINTS … IMMEDIATE, the specified constraints are checked. If they fail, SET CONSTRAINTS raises an exception. If, instead of a SET CONSTRAINTS … IMMEDIATE statement, you execute COMMIT and the constraints are found to be False, COMMIT instead performs a ROLLBACK.
Bottom line: You can defer the constraints only within a transaction. When the transaction is terminated by a ROLLBACK or a COMMIT, the constraints are both enabled and checked. The SQL capability of deferring constraints is meant to be used within a transaction. If used properly, the terminated transaction doesn’t create any data that violates a constraint available to other transactions.