Mysql – Why to use foreign keys with no action on delete or update

foreign-keysinnodbmyisamMySQL

I have a question of interest:

I have 2 tables in with InnoDb.
table tbl_a has a primary key, named a_id;
table tbl_b has a primary b_id and a foreign key on tbl_a.a_id with "ON DELETE NO ACTION".

+-------------+---------------+---------------+
|  Table Name |  Primary Key  |  Foreign Key  |
+-------------+---------------+---------------+
|    tbl_a    |     a_id      |               |
|    tbl_b    |     b_id      |     a_id      |
+-------------+---------------+---------------+

why should I still use InnoDb and foreign keys, if i don't really use the magic of foreign keys in the end in anyway?
Is there still a point of using
and foreign keys
instead of
and no foreign keys.
If I just do "NO ACTION" on deletes or updates?

I hope you got my point of interest 🙂

Best Answer

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 like user_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 like file.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.

Related Topic