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.
Referential integrity actions are triggered by physical, rather than logical, updates to the unique value. For example, even in a case-insensitive database, updating the primary key value from SAMPLE-VALUE to sample-value will trigger a referential integrity action, even though the two values are logically the same.
You can specify each of the following 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.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |