Scribd
Upload
46 views

Referential Actions: by Neil A. Basabe

MySQL supports five options for referential actions on foreign keys when an update or delete occurs on a parent table: 1. CASCADE - Delete or update the row from the parent table and automatically delete or update matching rows in the child table. 2. SET NULL - Delete or update the row from the parent table and set the foreign key columns in the child table to NULL. 3. RESTRICT - Rejects the delete or update operation for the parent table if there are matching rows in the child table. 4. NO ACTION - Same as RESTRICT, rejects the delete or update for the parent table if child rows exist. 5. SET DEFAULT - Recognized by

Uploaded by

Neil Basabe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
46 views

Referential Actions: by Neil A. Basabe

MySQL supports five options for referential actions on foreign keys when an update or delete occurs on a parent table: 1. CASCADE - Delete or update the row from the parent table and automatically delete or update matching rows in the child table. 2. SET NULL - Delete or update the row from the parent table and set the foreign key columns in the child table to NULL. 3. RESTRICT - Rejects the delete or update operation for the parent table if there are matching rows in the child table. 4. NO ACTION - Same as RESTRICT, rejects the delete or update for the parent table if child rows exist. 5. SET DEFAULT - Recognized by

Uploaded by

Neil Basabe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Referential

Actions

by Neil A. Basabe
For storage engines supporting foreign keys,
MySQL rejects any INSERT or UPDATE operation
that attempts to create a foreign key value in a child
table if there is no matching candidate key value in
the parent table.

When an UPDATE or DELETE operation


affects a key value in the parent table that has
matching rows in the child table, the result depends
on the referential action specified using ON UPDATE
and ON DELETE subclauses of the FOREIGN KEY
clause.
MySQL supports · CASCADE: Delete or update the row from the parent
five options table, and automatically delete or update the matching
regarding the rows in the child table. Both ON DELETE CASCADE
and ON UPDATE CASCADE are supported. Between
action to be taken, two tables, do not define several ON UPDATE

listed here:
CASCADE clauses that act on the same column in the
parent table or in the child table.
Note: Cascaded foreign key actions do not activate
triggers.
● CASCADE

· SET NULL: Delete or update the row from the


● SET NULL

● RESTRICT parent table, and set the foreign key column or


columns in the child table to NULL. Both ON DELETE
● NO ACTION SET NULL and ON UPDATE SET NULL clauses are
supported.
● SET DEFAULT If you specify a SET NULL action, make sure that
you have not declared the columns in the child table
as NOT NULL.
· RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT
(or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

· NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The


MySQL Server rejects the delete or update operation for the parent table if there is a related
foreign key value in the referenced table. Some database systems have deferred checks, and
NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately,
so NO ACTION is the same as RESTRICT.

· SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB
reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT
clauses.

For an ON DELETE or ON UPDATE that is not specified, the default action is


always RESTRICT.

You might also like