Microsoft KB Archive/150874

= INF: Deferred Constraints and Disable_Def_Cnst_Chk =

Article ID: 150874

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q150874



SUMMARY
SQL Server version 6.5 has the ability to disable deferred constraint checking. Deferred constraints are described in this article; examples showing the effects of disabling them with the use of SET Disable_Def_Cnst_Chk ON are also provided.



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

Msg 547, Level 16, State 2

UPDATE statement conflicted with COLUMN REFERENCE constraint

'FK__def_emplo__mgr_i__496EF0FC'. The conflict occurred in database

'pubs', table 'def_employee', column 'mgr_id'

Command has been aborted.

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.

Keywords: kbhowto kbusage KB150874

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.