Cascade Technique for Enforcing Referential Integrity
Cascading is the third available option in response to a violation of a referential integrity constraint. (Cascading means that the original update applies to other records that violate the constraint.) If the statement that violates the constraint is:
• An insert or update, cascading consists of inserting the offending foreign key into the primary key column.
• A delete, cascading means not only deleting the primary key, but also deleting all foreign keys that match that primary key.
The database procedure shown in this example, delete_children, can be used to implement a cascading delete rule. The procedure can be invoked by a rule, when a parent row is deleted, to delete all child entries belonging to that parent:
Parameters
me varchar(10)
Declare Section
msg varchar(80) not null;
Statements
msg = 'Deleting child(ren) from "' + :me + '"';
message :msg;
delete from person where parent = :me;
if iirowcount > 0 then
msg = 'Deleted ' + varchar(:iirowcount) +
' child(ren) from "' + :me + '"';
else
msg = 'No children deleted from "' + :me + '"';
endif;
message :msg;
After defining this database procedure, create a rule to invoke it after deletes, and enter the following for the procedure parameters:
me = old.name
When the rule is fired after the initial delete statement, it executes the delete_children database procedure, which deletes all children whose parent is the current person. Each delete statement in the delete_children procedure, in turn, also fires the delete rule, until a particular person has no descendants. The message statements that are executed before and after a row is deleted demonstrate the order in which the tree is traversed.
Note: In this example, the person table is self-referencing, and functions like a self-join. Referential integrity does not require two separate tables. Here the primary key is name and the foreign key is parent, both of which are in the person table.