Editing Foreign key (section)

== Referential actions ==
{{main|Propagation constraint}}
Because the [[database management system]] enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated).  If dependent rows in referencing tables still exist, those references have to be considered. [[SQL:2003]] specifies 5 different '''referential actions''' that shall take place in such occurrences:
* [[#CASCADE|CASCADE]]
* [[#RESTRICT|RESTRICT]]
* [[#NO ACTION|NO ACTION]]
* [[#SET NULL|SET NULL]]
* [[#SET NULL, SET DEFAULT|SET DEFAULT]]

=== CASCADE ===
Whenever rows in the parent (referenced) table are deleted (or updated), the respective rows of the child (referencing) table with a matching foreign key column will be deleted (or updated) as well. This is called a cascade delete (or update).

=== RESTRICT ===
A value cannot be updated or deleted when a row exists in a referencing or child table that references the value in the referenced table.

Similarly, a row cannot be deleted as long as there is a reference to it from a referencing or child table.

To understand RESTRICT (and CASCADE) better, it may be helpful to notice the following difference, which might not be immediately clear. The referential action CASCADE modifies the "behavior" of the (child) table itself where the word CASCADE is used. For example, ON DELETE CASCADE effectively says "When the referenced row is deleted from the other table (master table), then delete ''also from me''". However, the referential action RESTRICT modifies the "behavior" of the master table, ''not'' the child table, although the word RESTRICT appears in the child table and not in the master table! So, ON DELETE RESTRICT effectively says: "When someone tries to delete the row from the other table (master table), prevent deletion ''from that other table'' (and of course, also don't delete from me, but that's not the main point here)."

RESTRICT is not supported by Microsoft SQL 2012 and earlier.

=== NO ACTION ===
NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the [[update (SQL)|UPDATE]] or [[delete (SQL)|DELETE]] statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the [[Database trigger|triggers]] or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.

=== SET NULL, SET DEFAULT ===
In general, the action taken by the [[DBMS]] for SET NULL or SET DEFAULT is the same for both ON DELETE or ON UPDATE: the value of the affected referencing attributes is changed to NULL for SET NULL, and to the specified default value for SET DEFAULT.

=== Triggers ===
Referential actions are generally implemented as implied [[Database trigger|triggers]] (i.e. triggers with system-generated names, often hidden.) As such, they are subject to the same limitations as user-defined triggers, and their order of execution relative to other triggers may need to be considered; in some cases it may become necessary to replace the referential action with its equivalent user-defined trigger to ensure proper execution order, or to work around mutating-table limitations.

Another important limitation appears with [[Isolation (database systems)|transaction isolation]]: your changes to a row may not be able to fully cascade because the row is referenced by data your transaction cannot "see", and therefore cannot cascade onto. An example: while your transaction is attempting to renumber a customer account, a simultaneous transaction is attempting to create a new invoice for that same customer; while a CASCADE rule may fix all the invoice rows your transaction can see to keep them consistent with the renumbered customer row, it won't reach into another transaction to fix the data there; because the database cannot guarantee consistent data when the two transactions commit, one of them will be forced to roll back (often on a first-come-first-served basis.)

<syntaxhighlight lang="sql">
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

CREATE TRIGGER ins_sum BEFORE INSERT ON account
     FOR EACH ROW SET @sum = @sum + NEW.amount;
</syntaxhighlight>