6.10 How do I…Create a deferred constraint?

Problem

I have an application that needs to update the primary key of a parent table. To maintain the master-detail relationship, foreign keys in child tables must also be updated. I cannot perform this operation unless I disable the underlying constraints. Disabling constraints for every cascading update operation is not practical apart from the fact that enabling constraints after such updates is slow as we have large tables. I know that with Oracle8 there is a powerful new functionality of deferred constraints that permit cascade updates without having to disable constraints. How do I create a deferred constraint?

Technique

Deferred constraints is a new Oracle8 feature that allows constraint checking to be deferred until the end of the transaction. For any deferred constraint, the system checks that it is satisfied on commit in contrast to an immediate constraint that is checked at the end of each statement. Deferred constraints ensure that constraint processing time remains constant as the database grows in size and complexity. Multiple related tables can be manipulated in an arbitrary order within a transaction. When the transaction is committed, constraint checking on foreign keys is done to determine referential integrity violations. While performing bulk insert or update operations on a single table, constraint checking of primary key, NOT NULL, and check constraints can be deferred until the end of the transaction. A deferrable constraint can be specified with the constraint definition in the CREATE TABLE statement or can be added later using an ALTER TABLE statement. In a transaction, the SET CONSTRAINT statement can be issued to change the constraint mode of a deferrable constraint to deferred or immediate. A non-deferrable constraint is always in an immediate mode and cannot be set to a deferred mode.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP6_34.SQL, shown in Figure 6.34, drops the EMP06 table and creates the table again with a DEFERRABLE foreign key constraint, and also creates sample data used in this How-To.

SQL> START CHP6_34

SQL> SET ECHO OFF

Table dropped.

Table created.

1 row deleted.

1 row created.

1 row created.

1 row created.

1 row created.

Any table or column constraint can be defined as deferrable. In this example, we choose to create a deferrable foreign key constraint. The DEFERRABLE keyword means that the constraint will not be checked until the transaction is committed. A constraint can be specified to be NOT DEFERRABLE, which means that for every single row being manipulated, the constraint will be checked. NOT DEFERRABLE is the default. When you specify a constraint to be DEFERRABLE, you can additionally specify the initial state of the constraint. With the INITIALLY DEFERRED keywords, the transaction is started in a deferred mode, and constraint checking is postponed until a SET CONSTRAINT IMMEDIATE statement is issued, or the end of transaction signaled by a COMMIT statement. With the INITIALLY IMMEDIATE keywords, the constraint starts in a non-deferred mode, that is, the constraint is checked immediately after each statement. The default is INITIALLY IMMEDIATE. An ALTER TABLE statement can be used to add a deferred constraint to an existing table.

2. Note that the foreign key constraint on EMP06 table is still not in a DEFERRED mode, even though it was defined as DEFERRABLE. As no initial state was specified in the constraint definition, the constraint starts with the default mode of INITIALLY IMMEDIATE. Run the CHP6_35.SQL file, as shown in Figure 6.35, which contains a SET CONSTRAINT statement to set the initial state of the constraint to DEFERRED.

The SET CONSTRAINT statement is used to change the mode of a con-straint. SET CONSTRAINT is followed by the name of constraint it needs to act upon. You can use SET CONSTRAINT (with an optional trailing S for plural purists) with a list of constraint names separated by commas to change the states of multiple deferrable constraints in communion using a single statement. The ALL keyword can also be used instead of the con-straint names to apply the change to all deferrable constraints at once. The DEFERRED keyword indicates that the conditions specified by the deferrable constraint are checked when the constraint state is changed to IMMEDIATE, or the transaction is committed with COMMIT.

3. Run the CHP6_36.SQL file, which contains an UPDATE statement to modify a primary key value in the DEPT06 table that is being referenced by a record in the EMP06 table. The results of the operation are shown in Figure 6.36.

Because the foreign key was deferred in the previous step, the UPDATE statement succeeds in modifying the DEPT_NO value of a record that is referenced in the EMP06 table. Had the constraint not been deferred, such a statement would fail. As the deferred mode initiated by the SET CONSTRAINT DEFERRED statement is effective, this interim state where the foreign key constraint is violated is allowed by the system.

With Oracle7, you would have to disable the foreign key constraint or add temporary rows to the EMP06 table to avoid constraint violations. Disabling and enabling the constraint leaves a time window in between where the table is unprotected. Adding temporary rows is an equally undesirable solution with extraneous complexity in the application.

4. Run the statement in file CHP6_37.SQL to query the USER_CONSTRAINTS view. Figure 6.37 shows the results of the query. The DEFERRABLE column contains the mode of the constraint—DEFERRABLE or NOT DEFERRABLE, depending on the way it was created.

The FKEY_DEPT is correctly shown as a DEFERRABLE constraint. The current state of the constraint can be queried from the DEFERRED column of the USER_CONSTRAINTS view. The last column has a value of VALIDATED that indicates that constraints are in an enabled state. When a constraint is either disabled or enforced after it was disabled, you will see a NOT VALIDATED value in this column, as validation takes place only when the constraint is enabled. Only after validation has completed successfully and the constraint is enabled, this column will show a value of VALIDATED.

5. Run the CHP6_38.SQL file, as shown in Figure 6.38, which contains a SET CONSTRAINT statement to set the state of all deferred constraints to IMMEDIATE.

The SET CONSTRAINT statement is used to set the constraint mode to either DEFERRED or IMMEDIATE in a transaction. The IMMEDIATE keyword indicates that you want to check whether any constraints remain violated before committing the transaction. The statement effectively checks whether it is safe to issue a COMMIT statement. If the SET CONSTRAINT statement fails due to any constraint violations, an ORA-02292 error is returned by Oracle but the transaction is not rolled back. The name of the constraint violated appears in the error message. This gives an opportunity to examine and fix constraint violations. This SET CONSTRAINT statement can be issued any number of times until it succeeds when there are no remaining constraint violations.

6. Run the statements in the CHP6_39.SQL file. It has an UPDATE statement to correct the data in the EMP06 table. A SET CONSTRAINT ALL IMMEDIATE statement executes successfully after the constraint violating row has been adjusted. A COMMIT is finally issued to complete the transaction. Figure 6.39 displays the results of the operation in SQL*Plus.

If a COMMIT statement was issued before fixing the constraint violation, the transaction would have been rolled back. The SET CONSTRAINT mode lasts until another SET CONSTRAINT statement toggles the mode or until the end of transaction. The ALTER SESSION statement provides an alternative means of setting the constraint mode and is equivalent to the SET CONSTRAINT ALL statement. The SET CONSTRAINT statement cannot be used in triggers.

How It Works

Step 1 creates the EMP06 table with a deferrable foreign key constraint. Step 2 actually sets the FKEY_DEPT constraint in the deferred mode by issuing a SET CONSTRAINT…DEFERRED statement. Step 3 updates the primary key value of a row in the DEPT06 table that is referenced as a foreign key value by a row in the EMP06 table. A primary key value must exist in the parent table before a foreign key in the child table can reference it. As constraint checking is in a deferred mode, the system does not complain about the foreign key constraint violation. Steps 4 queries the USER_CONSTRAINTS view to list relevant constraint information. Step 5 issues a SET CONSTRAINT…IMMEDIATE statement to set the constraint back to immediate mode. Constraints are checked at this point, and an error is returned due to the constraint violation that was intentionally introduced in Step 3. The constraint remains in deferred mode but the transaction is not rolled back. In order to satisfy the foreign key constraint, Step 6 updates the foreign key value to that of the new primary key value that was set in Step 3. A SET CONSTRAINT…IMMEDIATE statement now executes successfully. Subsequently, a COMMIT statement signals the end of the transaction and changes to data are made permanent.

Comments

Oracle8 allows constraint checking to be deferred until the end of the transaction. A deferred constraint is checked at the end of the transaction, and if any data still violates the constraint upon commit, the transaction is rolled back. By changing the constraint mode to immediate from deferred, constraint violations can be checked to see if it is safe to issue a commit or not. If there are constraint violations, an error is returned but the transaction is not rolled back. You can then correct the error before committing the transaction. Both deferrable and non-deferrable constraints can use non-unique indexes, thus eliminating redundant indexes.