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

Question

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 :)

Doesn't it ensure that the foreign keys correspond to actual, valid rows? — Waleed Khan, Aug 23 '12 at 17:09
If you delete foreign row your data is not complete, and you are going to have big mess in DB. — Peter, Aug 23 '12 at 17:09
summary looks like, `ON DELETE NO ACTION` will stop invalid deletes/updates, so it is good to have — Manohar Reddy Poreddy, Apr 28 '21 at 03:37

ruakh · Accepted Answer · 2016-07-29T22:13:57.977

93

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.

edited Jul 29 '16 at 22:13

answered Aug 23 '12 at 17:14

ruakh

175,680
26
273
307

ohhhh ok, thank you very much... well then I truly misunderstood "NO ACTION" – Preexo Aug 23 '12 at 17:18
1

but why does phpmyadmin offer both: "NO ACTION" and "RESTRICT"? do they misunderstand it too? – Preexo Aug 23 '12 at 17:23
@user1011116: Well, MySQL supports both notations, so I guess phpmyadmin feels that it should offer them both as well? And I could imagine a storage engine handling them differently -- some DBMSes do handle them slightly differently (as mentioned in the documentation I linked to) -- so perhaps phpmyadmin feels that it's more future-proof to let users choose. – ruakh Aug 23 '12 at 17:35
2

I agree with the asker, here. "NO ACTION" does not communicate the same thing as "DEFAULT ACTION". To me, "NO ACTION" seems to indicate "don't do anything," which would also be nonsensical. – ryvantage Apr 17 '14 at 18:09
@ryvantage: I'm not sure what the purpose of your comment is -- I'm not MySQL, so it doesn't make sense to argue with me about what it *should* mean -- but let me try to address it anyway . . . It's not that "NO ACTION" means "DEFAULT ACTION"; rather, it's that there's no default action. When you delete a row in a way that would violate the constraint, you can specify an action for MySQL to take to rectify the situation; but if you don't do so, then it won't take any action, so the statement continues to violate the constraint. So, it's rejected. – ruakh Apr 17 '14 at 21:26
@ruakh, that confused me even further. Your original answer said `It means the same as ON DELETE RESTRICT (the default)`. But now you're saying it doesn't mean to do the default action?? I'm confused. And, yes, any hostility you may perceive towards you was indeed unintended. I even realized that the comment was not written well when I first wrote it and tried to edit it accordingly. Apparently I failed. Sorry. – ryvantage Apr 17 '14 at 23:15
@ryvantage: There's no default action: by default, it does no action, letting the error happen (due to the constraint violation). If you want to explicitly specify that there should be no action, and therefore an error, you can write `ON DELETE RESTRICT` or `ON DELETE NO ACTION`, but these are both equivalent to the default behavior, which is not to take any action, letting the error happen. If you still don't see it, then I'm not sure I can make it any clearer . . . – ruakh Apr 18 '14 at 05:31
Thanks. I find this name pretty confusing. IIUC, "NO ACTION" means that the system DOES react to noticing that a foreign key constraint would be violated by the current mutation statement (i.e. update or delete): the reaction is to return to the state that existed prior to the (in progress) mutation being enacted. Also, IIUC, "NO ACTION" does what most people would consider "the most sensible thing", which esp makes sense, since "NO ACTION" is implied in the absence of explicit specified action. – allyourcode Feb 15 '15 at 09:18
@allyourcode If you want "no action" in that sense, why define the constraint in the first place? – tobiv Jan 11 '18 at 19:54
@tobiv: I don't think allyourcode is suggesting that the DBMS should offer unenforced constraints as a DB feature; rather, (s)he's saying that `ON DELETE NO ACTION` is a poor name for what it does. – ruakh Jan 11 '18 at 21:02

score 9 · Answer 2 · edited Apr 17 '14 at 21:23

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.

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

2 Answers2

Linked