Back
Home
Up
Next

Cascade declarative referential integrity.

    To be more compliant with the SQL standard, IB extended the syntax for declarative referential integrity through foreign keys. With this feature, users are able to tell the server at the time the foreign key is defined, what should be the action performed in details tables when a record in the master table updates its primary key or the whole record is deleted.

In IB4, this capability was limited to the implicit behavior of NO ACTION. This means that a PK in the master table that's referenced in a detail (child) table cannot be deleted or updated; otherwise, an error is reported. Those cases where handled in BEFORE triggers in the master table. Now, with the extension, the syntax for defining the constraint for a column at the column level, namely, inside the column definition, becomes:

<col_constraint> = [CONSTRAINT constraint] <constraint_def>
<constraint_def> = {
PRIMARY KEY
| UNIQUE
| CHECK ( <search_condition>)
| REFERENCES other_table [( other_col )]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}

and at the table level, namely, adding a constraint to the table after it has been created, becomes:

<tconstraint> = [CONSTRAINT constraint] <tconstraint_def>
< tconstraint_def> = {
{PRIMARY KEY | UNIQUE} ( col [, col ...])
| CHECK ( <search_condition>)
| FOREIGN KEY ( col [, col ...])
REFERENCES other_table [( other_col [, other_col ...])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}

Among other advantages, this approach allows a declarative sentence that's easy to read in an script, as opposed to user defined triggers. Also, triggers have disadvantages when compared to declarative referential integrity due to the multi versioning engine. Worse, when dealing with updates to the master table's PK, the trigger approach becomes tricky.

The available options for the behavior on the child table when deleting or updating the PK on the master table are:

  • [Default] NO ACTION: Does not change the foreign key; may cause the primary key update to fail due to referential integrity checks. This is equivalent to the only and implicit behavior in IB4.X.
  • CASCADE: For ON DELETE, deletes the corresponding foreign key; for ON UPDATE, updates the corresponding foreign key to the new value of the primary key.
  • SET NULL: Sets all the columns of the corresponding foreign key to NULL.
  • SET DEFAULT: Sets every column of the corresponding foreign key is set to its default value in effect when the referential integrity constraint is defined; when the default for a foreign column changes after the referential integrity constraint is defined, the change does not have an effect on the default value used in the referential integrity constraint.

Take into account that the cascade can be propagated among many levels of master/details relationships in the special case when the FK in the child table is part of the PK and the grandchild uses the whole PK of the child as its new FK. However, creating keys that span several fields is calling for performance problems in IB. When a PK or FK is too long due to FK propagation, it's better to use a surrogate key instead.

 

This page was last updated on 2002-10-09 07:16:23