What is MySQL’s default ON DELETE behavior?

by

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.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How do I quickly rename a MySQL database (change schema name)?
  3. How to debug Lock wait timeout exceeded on MySQL?
  4. Is there a REAL performance difference between INT and VARCHAR primary keys?
  5. MySQL AUTO_INCREMENT does not ROLLBACK
  6. 1114 (HY000): The table is full
  7. How to shrink/purge ibdata1 file in MySQL
  8. Restore table structure from frm and ibd files
  9. MySQL InnoDB not releasing disk space after deleting data rows from table
  10. What’s the difference between MyISAM and InnoDB? [duplicate]
  11. Why is MySQL InnoDB insert so slow?
  12. MySql: MyISAM vs. Inno DB! [closed]
  13. How to convert all tables from MyISAM into InnoDB?
  14. Prevent auto increment on MySQL duplicate insert
  15. UUID performance in MySQL?
  16. MySQL InnoDB foreign key between different databases
  17. Force InnoDB to recheck foreign keys on a table/tables?
  18. How to optimize COUNT(*) performance on InnoDB by using index
  19. Is there any performance gain in indexing a boolean field?
  20. Why does MySQL autoincrement increase on failed inserts?
  21. How can I check MySQL engine type for a specific table?
  22. MySQL InnoDB: autoincrement non-primary key
  23. How do I rename a MySQL database (change schema name)?
  24. How to regex in a MySQL query
  25. MySQL Integer vs DateTime index
  26. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  27. How do I lock on an InnoDB row that doesn’t exist yet?
  28. Defining Composite Key with Auto Increment in MySQL
  29. MySQL DROP all tables, ignoring foreign keys
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment