INF: Deferred Constraints and Disable_Def_Cnst_Chk (150874)

MORE INFORMATION

By default, constraints are checked during the execution of a statement for each row that the statement affects and then perhaps again after all rows have been modified. This is necessary because an UPDATE statement might make a change to a row that by itself might seem to violate a constraint, but when viewed with all of the subsequent changes to other rows (by the same statement execution), the table is left in a consistent state. As constraints are checked for each row, any apparent violations are marked for rechecking and "deferred." Once all rows have been updated, all marked rows have their constraints rechecked (this is "deferred constraint checking"); if any violations now occur, the execution of the statement fails; and if no violation occurs, the statement succeeds.

For example: The def_employee table has a primary key on emp_id and a self-referencing foreign key from the mgr_id to emp_id. The statements to create this table and populate it with 5 rows are given below. The inserts are followed by updates so the employees will not have to be inserted in order with the managers entered first.

DROP TABLE def_employee
go
CREATE TABLE def_employee (
   emp_id INT NOT NULL PRIMARY KEY,
   name CHAR(10),
   mgr_id INT NULL REFERENCES def_employee)
go
INSERT def_employee VALUES ( 1, 'VP', NULL)
INSERT def_employee VALUES ( 2, 'PRES', NULL)
INSERT def_employee VALUES ( 4, 'JOE', NULL)
INSERT def_employee VALUES ( 6, 'CEO', NULL)
INSERT def_employee VALUES ( 8, 'MGR', NULL)
UPDATE def_employee SET mgr_id = 2 WHERE emp_id = 1
UPDATE def_employee SET mgr_id = 6 WHERE emp_id = 2
UPDATE def_employee SET mgr_id = 8 WHERE emp_id = 4
UPDATE def_employee SET mgr_id = 6 WHERE emp_id = 6
UPDATE def_employee SET mgr_id = 1 WHERE emp_id = 8
SELECT * FROM def_employee
				

This gives the following table:

emp_id      name       mgr_id
----------- ---------- -----------
1           VP         2
2           PRES       6
4           JOE        8
6           CEO        6
8           MGR        1
				

When the following UPDATE statement is executed, if the rows were considered individually, the UPDATE should fail for every row in the table (because of a missing primary key for its mgr_id value) except for CEO; but even that should fail because that would leave PRES without a primary key for its manager. However, the UPDATE statement succeeds without any constraint errors because it uses deferred constraint checking.

UPDATE def_employee
  SET emp_id = emp_id + 1000,
      mgr_id = mgr_id + 1000

SELECT * FROM def_employee
				

Gives the result:

emp_id      name       mgr_id
----------- ---------- -----------
1001        VP         1002
1002        PRES       1006
1004        JOE        1008
1006        CEO        1006
1008        MGR        1001
				

For an operation on a large table, deferred constraint checking may cause a loss in performance. An UPDATE may affect thousands of rows only to find that many of the constraint violations that it found as it was checking each row are still violations once it does the deferred constraint checking. Of course, all it has to find is the first failure in the deferred phase, and then it will have to cancel the statement and rollback all of the changes. It may take quite a long time to perform the operation and all of the checking, plus the rolling back (the system is optimized for going forward, not rolling back). It might have been better if the first failed constraint it encountered had caused the entire operation to fail rather than perform all that additional processing only to confirm that it could have failed earlier.

DISABLING DEFERRED CONSTRAINT CHECKING

In SQL Server 6.5, it is possible to disable deferred constraint checking by using the command:

SET DISABLE_DEF_CNST_CHK ON

With this option on, the reverse of the above operation will fail (as would the original operation):

SET DISABLE_DEF_CNST_CHK ON
GO
UPDATE def_employee
  SET emp_id = emp_id - 1000,
      mgr_id = mgr_id - 1000
				

Notice that the UPDATE fails even though it is valid and would leave the table in a consistent state with all constraints satisfied. On a very large table, the apparent speed will be much improved, although it may fail when it actually should not have.

The deferred constraint behavior depends on the current setting of Disable_Def_Cnst_Chk when the operation is performed, not what its value was when the table or constraint was created.

The behavior with deferred constraint checking shut off is similar to a poorly written trigger that only validates operations a row at a time rather than the result of the operation as a whole.

ANSI specifies that deferred constraint checking should occur (the Disable_Def_Cnst_Chk option should be OFF). Higher levels of the ANSI standard (not implemented in SQL Server 6.5) allow constraints to be specified with terms like 'Initially Deferred', 'Initially Immediate', and '[Not] Deferrable' to customize this behavior at the constraint-level.