Deferring the Checking of Constraints

Deferring the Checking of Constraints

Suppose the situation of "Declaring Foreign-Key Constraints" Example 1, where presC# in Studio is a foreign key referencing cert# of MovieExec. Bill Clinton decides, after his national presidency, to found a movie studio, called Redlight Studios, of which he will naturally be the president. If we execute the insertion:

we are in trouble. The reason is that there is no tuple of MovieExec with certificate number 23456 (the presumed newly issued certificate for Bill Clinton), so there is an obvious violation of the foreign-key constraint.

One possible fix is first to insert the tuple for Redlight without a president's certificate. as:

This change keeps away from the constraint violation, because the Redlight tuple is inserted with NULL as the value of presC#, and NULL in a foreign key does not require that we check for the existence of any value in the referenced column.

On the other hand, we must insert a tuple for Bill Clinton into MovieExec, with his correct certificate number before we can apply an update statement such as

If we do not fix MovieExec first, then this update statement will also violate the foreign-key constraint.

Certainly, inserting Bill Clinton and his certificate number into MovieExec before inserting Redlight into Studio will of course protect us against a foreign-key violation in this case. On the other hand, there are cases of circular constraints that cannot be fixed by judiciously ordering the database modification steps we take.

Example 1 : If movie executives were limited to studio presidents, then we might want to declare cert# to be a foreign key referencing Studio(presC#); we would then have to declare presC# to be UNIQUE, but that declaration makes sense if you assume a person cannot be the president of two studios at the same time.

Now, it is impossible to insert new studios with new presidents. We can't insert a tuple with a new value of presC# into Studio, because that tuple would violate the foreign-key constraint from presC# to MovieExec(cert#). We can't insert a tuple with a new value of cert# into MovieExec, because that would violate the foreign-key constraint from cert# to Studio(presC#).

The problem of Example 1 has a solution, but it involves numerous elements of SQL that we have not yet seen.

1. First, we need the ability to group numerous SQL statements (the two insertions - one into Studio and the other into MovieExec) into one unit called a "transaction." We shall meet transactions as an indivisible unit of work in "Transactions in SQL".

2. Then, we need a way to tell the SQL system not to check the constraints until after the whole transaction is finished ("committed" in the terminology of transactions).

We may take point (1) on faith for the moment, but there are two details we must learn to handle point (2):

a)  Any constraint - key, foreign-key, or other constraint types we shall meet later in this section - may be declared DEFERRABLE or NOT DEFERRABLE. The latter is the default, and means that every time a database modification occurs, the constraint is checked immediately afterwards, if the modification requires that it be checked at all. However, if we declare a constraint to be DEFERRABLE, then we have the option of telling it to wait until a transaction is complete before checking the constraint.

b)  If a constraint is deferrable, then we may also declare it to be INITIALLY DEFERRED or INITIALLY IMMEDIATE. In the former case, checking will be deferred to the end of the current transaction, unless we tell the system to stop deferring this constraint. If declared INITIALLY IMMEDIATE, the check will be made before any modification, but because the constraint is deferrable, we have the option of later deciding to defer checking.

Example 2 : Figure 1 shows the declaration of Studio modified to allow the checking of its foreign-key constraint to be deferred until after each transaction. We have also declared presC# to be UNIQUE, in order that it may be referenced by other relations foreign-key constraints.

If we made a similar declaration for the hypothetical foreign-key constraint from MovieExec(cert#) to Studio(presC#) mentioned in Example 1, then we could write transactions that inserted two tuples, one into each relation, and the two foreign-key constraints would not be checked until after both insertions had been done. Then, if we insert both a new studio and its new president, and use the same certificate number in each tuple, we would avoid violation of any constraint.

There are two further points about deferring constraints that we should keep in mind:

●  Constraints of any type can be given names. We shall discuss how to do so in "Modification of Constraints".

●  If a constraint has a name, say MyConstraint, then we can change a deferrable constraint from immediate to deferred by the SQL statement

and we can reverse the process by changing DEFERRED in the above to IMMEDIATE.