33

In foreign key options on update and on delete. What does each field [Restrict, Cascade, Set Null, No Action] do?

user2310173
  • 331
  • 1
  • 3
  • 3
  • 5
    If everything else fails, read the manual: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html –  Apr 23 '13 at 07:29

3 Answers3

45

If you take one by one :

For both update and delete :

if you try to update / delete the parent row :

Restrict : Nothing gonna be delete if there is a child row

Cascade : the child row will be delete / update too

Set Null : the child column will be set to null if you delete the parent

No action : The child row will not be concern of the delete / update

Parth kharecha
  • 6,135
  • 4
  • 25
  • 41
Axel Agarrat
  • 589
  • 1
  • 4
  • 6
  • 5
    "restrict" and "no action" are the same. Restrict is a mysql only keyword while "no action" is the SQL standard. – ProfileTwist Feb 21 '14 at 07:14
  • 1
    @ProfileTwist No, what I understand is "restrict" will cancel the update/delete operation if there are records (you have to delete child rows first), on contrary "no action" will let you update/delete the record even if there are child rows – glautrou Jun 10 '14 at 12:19
  • 2
    @glautro Check out http://stackoverflow.com/questions/5809954/mysql-restrict-and-no-action . Perhaps some engines work as you describe but in Innodb it is impossible to update/delete the parent row and create orphan child rows unless foreign key checks are disabled temporarily. This is terrific for data integrity (if that is what you are trying to achieve with FK's) – ProfileTwist Jun 13 '14 at 09:51
31

The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table.

Set NULL : Sets the column value to NULL when you delete the parent table row.

CASCADE : CASCADE will propagate the change when the parent changes. If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.

RESTRICT : RESTRICT causes you can not delete a given parent row if a child row exists that references the value for that parent row.

NO ACTION : NO ACTION and RESTRICT are very much alike. when an UPDATE or DELETE statement is executed on the referenced table, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. in short child row no concern if parent row delete or update.

Community
  • 1
  • 1
Jaykumar Patel
  • 26,836
  • 12
  • 74
  • 76
  • 1
    Rephrasing `in short child row no concern if parent row delete or update`, does that mean that, in NO ACTION, if the parent is deleted, this is not allowed? In case of parent Update, does the child remain the same? – Pathros Jun 03 '15 at 14:26
  • @pathros no dear , it means no change will occur on child table , only parent table will be effected - either it is delete or update - which means only parent will be deleted (in delete case) and all childs will be kept as they will be – Mani May 22 '16 at 20:06
  • 1
    and i know that it forces us to ask then why it's alike `Restrict` , still searching the answer - if anyone got the logic , kindly share – Mani May 22 '16 at 20:12
  • Which one is the parent and which the child? An example would really help :-) – starbeamrainbowlabs Nov 09 '16 at 10:56
1

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 five options regarding the action to be taken, listed here:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

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 InnoDB rejects 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.

Copied above text from: https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

Hemanth
  • 723
  • 7
  • 15