Why to use foreign keys with no action on delete or update
foreign-keys, innodb, myisam, mysql
Solution
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.
Problem
I have a question of interest: I have 2 tables in mysql 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 innodb and foreign keys instead of myisam and no foreign keys. If I just do "`NO ACTION`" on deletes or updates? I hope you got my point of interest :)