Take the 2-minute tour ×

I have a question of interest:

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

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 foreignkeys instead of myisam and no foreignkeys if I just do "NO ACTION" on deletes or updates?

I hope you got my point of interest :)

share|improve this question
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
add comment

2 Answers

up vote 5 down vote accepted

I think you're misunderstanding what ON DELETE NO ACTION means. It means the same as ON DELETE RESTRICT (the default); that is, it means that you simply can't delete a record that is referenced by a child record, and you'll get an error if you try. See §§ "FOREIGN KEY Constraints" and "Using FOREIGN KEY Constraints" in the MySQL 5.6 Reference Manual.

share|improve this answer
ohhhh ok, thank you very much... well then I truly misunderstood "NO ACTION" –  Preexo Aug 23 '12 at 17:18
@user1011116: You're welcome! –  ruakh Aug 23 '12 at 17:19
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
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 at 18:09
show 3 more comments

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.


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.

share|improve this answer
thanks such an explenation was what I was looking for... –  Preexo Aug 23 '12 at 17:12
-1, sorry. Technically speaking, every single statement in your answer is correct; but the overall effect of your answer is to paint a highly misleading picture of what NO ACTION means. –  ruakh Aug 23 '12 at 17:15
@ruakh Already edited out. –  Michael Berkowski Aug 23 '12 at 17:15
@MichaelBerkowski: It looks like you've changed your answer to implicitly acknowledge that NO ACTION is the default -- which is good, and I've now edited my answer to mention that as well -- but that wasn't my problem with your answer, anyway. –  ruakh Aug 23 '12 at 17:18
@ruakh clear enough for you? –  Michael Berkowski Aug 23 '12 at 17:20
show 2 more comments

Your Answer


