|
Maintaining referential integrity when updating or deleting a referenced primary key can be as simple as disallowing the update or drop. Often, however, it is also possible to take a specific action on each foreign key to maintain referential integrity. The CREATE TABLE and ALTER TABLE statements allow database administrators and table owners to specify what action to take on foreign keys that reference a modified primary key when a breach occurs.
You can specify each of the available referential integrity actions separately for updates and drops of the primary key:
RESTRICT Generates an error and prevents the modification if an attempt to alter a referenced primary key value occurs. This is the default referential integrity action.
SET NULL Sets all foreign keys that reference the modified primary key to NULL.
SET DEFAULT Sets all foreign keys that reference the modified primary key to the default value for that column (as specified in the table definition).
CASCADE When used with ON UPDATE, this action updates all foreign keys that reference the updated primary key to the new value. When used with ON DELETE, this action deletes all rows containing foreign keys that reference the deleted primary key.
System triggers implement referential integrity actions. The trigger, defined on the primary table, is executed using the permissions of the owner of the secondary table. This behavior means that cascaded operations can take place between tables with different owners, without additional permissions having to be granted.