Cascading referential integrity

Since InterBase® v5/Firebird, cascading referential integrity is also supported.

When a foreign key relationship is specified, the user can define which action should be taken following changes to, or deletion of its referenced primary key. ON UPDATE defines what happens when the primary key changes and ON DELETE specifies the action to be taken when the referenced primary key is deleted. In both cases the following options are available:

1. NO ACTION: throws an exception if there is a existing relationship somewhere in another table:

2. CASCADE: the foreign key column is set to the new primary key value. A very handy function when it comes to updating, as all referenced foreign key fields are automatically updated. When deleting the CASCADE option also deletes the foreign key row when the primary key is deleted. Be extremely careful when using CASCADE ON DELETE; when you delete a customer, you delete his orders, order lines, address, everything where there is a defined key relationship. It is safer to write a procedure that ensures just those data sets necessary are deleted in the right order.

3. SET NULL: if the foreign key value is allowed to be NULL, when a primary key value is deleted, it will set the relevant foreign key fields referencing this primary key value also to NULL.

4. SET DEFAULT: the foreign key column is set to its default value when a primary key field is deleted.