Why to use foreign keys with no action on delete or update
I think you're misunderstanding what ON DELETE NO ACTION
means. It does not mean to suppress the foreign-key constraint.
When you delete a record that is referred to by a foreign key, InnoDB has the ability to take an automatic action to rectify the situation:
- it can
CASCADE
, meaning, delete the referring record. (This would make sense for something likeuser_address.user_id
. If you hard-delete a user, you probably want to hard-delete all of the user's addresses as well.) - it can
SET NULL
, meaning, clear out the referring key. (This might make sense for something likefile.last_modified_by
. If you hard-delete a user, you might want the file's last-modified-by to become simply "unknown".)
If you specify NO ACTION
, you're telling InnoDB that you don't want it to take either of these actions. So InnoDB can't fix the situation for you; all it can do is reject the DELETE
and return an error.
As a result, ON DELETE NO ACTION
is actually the same as ON DELETE RESTRICT
(the default).
(Note: in some DBMSes, and in standard SQL, ON DELETE NO ACTION
is a bit different from ON DELETE RESTRICT
: in those, ON DELETE NO ACTION
means "accept the DELETE
within the current transaction, but reject the whole transaction if I try to commit it before rectifying the problem". But InnoDB doesn't support deferred checks, so it treats ON DELETE NO ACTION
exactly the same as ON DELETE RESTRICT
, and always rejects the DELETE
immediately.)
See ยงยง14.2.2.5 "FOREIGN KEY Constraints" and 13.1.17.2 "Using FOREIGN KEY Constraints" in the MySQL 5.6 Reference Manual.
The foreign key constraint even without ON DELETE / UPDATE CASCADE
ensures that if you insert a value into the child table, that it has a correctly matching value in the parent table (or is NULL
if the FK column is nullable). Attempting to insert an invalid value into the child table's FK column would result in error when the constraint fails, so your data integrity is protected.
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
Defining the foreign key constraint also implicitly defines an index on the FK column in the child table, which although you could have manually defined the index, will improve joining performance.
ON DELETE NO ACTION
(which is the same as omitting the ON DELETE
clause) will actively prevent deletion of a parent row if it is referenced by any child table, not passively allow it to be deleted without affecting child rows.