Update tables with referential constraints

Changing the key violates referential constraints for dependent tables and leaves some rows without a parent. Furthermore, you cannot give any part of a primary key a null value.

Update rules

The action taken on dependent tables when an UPDATE is performed on a parent table depends on the update rule specified for the referential constraint. If no update rule was defined for a referential constraint, the UPDATE NO ACTION rule is used.

UPDATE NO ACTION

Specifies that the row in the parent table can be updated if no other row depends on it. If a dependent row exists in the relationship, the UPDATE fails. The check for dependent rows is performed at the end of the statement.

UPDATE RESTRICT

Specifies that the row in the parent table can be updated if no other row depends on it. If a dependent row exists in the relationship, the UPDATE fails. The check for dependent rows is performed immediately.

The subtle difference between the RESTRICT rule and the NO ACTION rule is easiest seen when looking at the interaction of triggers and referential constraints. Triggers can be defined to fire either before or after an operation (an UPDATE statement, in this case). A before trigger fires before the UPDATE is performed and therefore before any checking of constraints. An after trigger is fired after the UPDATE is performed, and after a constraint rule of RESTRICT (where checking is performed immediately), but before a constraint rule of NO ACTION (where checking is performed at the end of the statement). The triggers and rules occur in the following order:

1. A before trigger is fired before the UPDATE and before a constraint rule of RESTRICT or NO ACTION.
2. An after trigger is fired after a constraint rule of RESTRICT, but before a NO ACTION rule.

If you are updating a dependent table, any non-null foreign key values that you change must match the primary key for each relationship in which the table is a dependent. For example, department numbers in the employee table depend on the department numbers in the department table. You can assign an employee to no department (the null value), but not to a department that does not exist.

If an UPDATE against a table with a referential constraint fails, all changes made during the update operation are undone.