Menu
I'm trying to parse the MySQL docs. They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.
NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you're saying NO ACTION (or RESTRICT -- same thing).
SET NULL allows a parent row deletion, sets the FK to NULL.
CASCADE deletes child row.
SET DEFAULT should just never be used.
Is this more or less correct?
449
For storage engines that support 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. For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION .
It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to NULL when the parent data is deleted or updated. SET DEFAULT. It is used in conjunction with ON DELETE or ON UPDATE.
The ON DELETE clause says that if a particular primary key ID value in the CUSTOMERS table is deleted, this action shall be prevented (this is the "restrict" part) if there is any row in the ORDERS table which has a foreign key that matches the value of the CUSTOMER table ID value.
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
Yes, it is correct:
NO ACTION: [...] InnoDB rejects the delete or update operation for the parent table.
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. [...]
Apparently NO ACTION and RESTRICT are synonymous. Additionally, since they are used whenever there is no ON DELETE / UPDATE clause, this is the default behavior.
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. [...]
The foreign column is set to NULL, provided it is not declared as NOT NULL (or InnoDB will not allow deletion or update).
CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. [...]
Cascade deletes (or updates) the foreign column.
SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
So basically you cannot use that option.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
