Rules
for insertion and deletion that assure referential integrity
Delete Rules
Deleting a row with a foreign key is always permitted.
When deleting a row with a primary key, DB2 takes action as indicated in the DDL; it either restricts deletion, cascades deletes to foreign key rows, or sets all referenced foreign keys to null.Each referential constraint must define the action that will be taken on foreign key rows when a primary key is deleted. There are four options that can be specified:
RESTRICT - Prevents deletion of a row from a parent table if children of the row still exist in a child table. If applied to our example above, this would make it illegal to delete an office if any sales representatives were still assigned to the office.
CASCADE - When a parent row is deleted, automatically delete all child rows. If applied to our example above, deleting an office would automatically delete every sales representative assigned to the office.
SET_NULL - When a parent row is deleted, automatically set all foreign key values to NULL. If applied to our example above, this would make deleting an office set every sales representative’s office assignment to an unknown office.
SET_DEFAULT - When a parent row is deleted, automatically set all foreign key values to their default values. See Advantage Data Dictionary for more information on default field values. If applied to our example above, this rule would assign sales representatives to some default office if their office were ever removed. The default office is stored within the data dictionary and is the default field value for the office field.
When inserting a row with a foreign key, DB2 checks the values of the foreign key columns against the values of the primary key columns in the parent table. If no matching primary key columns are found, the insert is disallowed.
NO ACTION: the behavior of NO ACTION is similar to RESTRICT. The only difference between RESTRICT and NO ACTION is when the referential constraint is enforced. RESTRICT enforces the delete rule immediately; NO ACTION enforces the delete rule at the end of the statement.
Inserted rules
A new primary key row can be inserted as long as the primary key is unique for the table.
When updating foreign key values, DB2 performs the same checks as when it is inserting a row with a foreign key.
If a primary key value is updated, DB2 does not allow there to be any existing foreign keys that refer back to the primary key that is changing. All foreign key rows first must be either deleted or be set to NULL before the value of the primary key can be changed.
Delete Rules
Deleting a row with a foreign key is always permitted.
When deleting a row with a primary key, DB2 takes action as indicated in the DDL; it either restricts deletion, cascades deletes to foreign key rows, or sets all referenced foreign keys to null.Each referential constraint must define the action that will be taken on foreign key rows when a primary key is deleted. There are four options that can be specified:
RESTRICT - Prevents deletion of a row from a parent table if children of the row still exist in a child table. If applied to our example above, this would make it illegal to delete an office if any sales representatives were still assigned to the office.
CASCADE - When a parent row is deleted, automatically delete all child rows. If applied to our example above, deleting an office would automatically delete every sales representative assigned to the office.
SET_NULL - When a parent row is deleted, automatically set all foreign key values to NULL. If applied to our example above, this would make deleting an office set every sales representative’s office assignment to an unknown office.
SET_DEFAULT - When a parent row is deleted, automatically set all foreign key values to their default values. See Advantage Data Dictionary for more information on default field values. If applied to our example above, this rule would assign sales representatives to some default office if their office were ever removed. The default office is stored within the data dictionary and is the default field value for the office field.
When inserting a row with a foreign key, DB2 checks the values of the foreign key columns against the values of the primary key columns in the parent table. If no matching primary key columns are found, the insert is disallowed.
NO ACTION: the behavior of NO ACTION is similar to RESTRICT. The only difference between RESTRICT and NO ACTION is when the referential constraint is enforced. RESTRICT enforces the delete rule immediately; NO ACTION enforces the delete rule at the end of the statement.
Inserted rules
A new primary key row can be inserted as long as the primary key is unique for the table.
When updating foreign key values, DB2 performs the same checks as when it is inserting a row with a foreign key.
If a primary key value is updated, DB2 does not allow there to be any existing foreign keys that refer back to the primary key that is changing. All foreign key rows first must be either deleted or be set to NULL before the value of the primary key can be changed.
Post a Comment