Using SQL Constraints Within Transactions
SQL constraints are important for data validation. Ensuring the validity of the data in your database means doing more than just making sure the data is of the right type. Perhaps some columns, for example, should never hold a null value — and maybe others should hold only values that fall within a certain range. Such restrictions are 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
Constraints that are
NOT DEFERRABLE are applied immediately. You can set
DEFERRABLE constraints to be either initially
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
EMPLOYEE.DeptNo is a foreign key that references the DEPT table. Assume also that the DEPT table has columns
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
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
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.
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
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
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.