INF: Deferred Constraints and Disable_Def_Cnst_Chk

• Microsoft SQL Server, version 6.5

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)

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

UPDATE def_employee

  SET emp_id = emp_id + 1000,
      mgr_id = mgr_id + 1000

Gives the result:

emp_id      name       mgr_id

1001        VP         1002
1002        PRES       1006
1004        JOE        1008
1006        CEO        1006
1008        MGR        1001

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

'FK__def_emplo__mgr_i__496EF0FC'. The conflict occurred in database
'pubs', table 'def_employee', column 'mgr_id'

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.