Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.

For example, if I have two tables - Parent and Child - with a foreign key on Child that references Parent and has ON DELETE CASCADE, which records trigger a cascade and which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

I wish the syntax was ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADE or something similar to remove the ambiguity. Does anyone have any mnemonics for remembering this?

share|improve this question
Well said! Also - I've realized this is the first time I've ever typed the word mnemonics. – d-_-b Sep 29 '15 at 23:52
up vote 71 down vote accepted

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

For consistency, the ON DELETE RESTRICT can be translated to the (less aggresive) You Can't Kill Parents! Only childless rows can be killed (deleted.)

share|improve this answer
I feel something is still missing in the analogy. Can't a child have more than one parent? In this case will killing one parent make the child an orphan? – Jus12 Nov 6 '14 at 13:31
@Jus12 No, foreign key constraints work with 1 parent only. It's not a good analogy regarding this aspect. – ypercubeᵀᴹ Nov 6 '14 at 13:33
@ypercube: Is this not allowed? Order(custID, itemID, orderID) where custID refers to a primary key in Customers table and itemID refers to a primary key in Items table. Won't Order have two parents? – Jus12 Nov 6 '14 at 15:32
@Jus12 That is allowed of course but it would be 2 foreign key constraints. Then every child (order) would have a parent (customer) and a parent (item). The behaviours of the 2 FKs might differ though. (So, for example, it could be that killing customers would kill all their (orders) children but killing items would not kill their orders.) – ypercubeᵀᴹ Nov 6 '14 at 15:45
The parent analogy can still work if we don't say "orphan". If there are two references to two separate parents on a child entry, this can still be seen as a child of a divorced couple. Restrict: "I won't let you kill my mom" Cascade: "If you kill my dad, I will also die" – Christopher McGowan Feb 28 '15 at 18:15

For example, if I have two tables - Parent and Child - where Child records are owned by Parent records, which table needs the ON DELETE CASCADE?

ON DELETE CASCADE is an optional clause in a foreign key declaration. So it goes with the foreign key declaration. (Meaning, in the "child" table.) could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

One way to interpret a foreign key declaration is, "All valid values for this column come from 'that_column' in 'that_table'." When you delete a row in the "child" table, nobody cares. It doesn't affect data integrity.

When you delete a row from the "parent" table--from "that_table"--you remove a valid value from the possible values for the "child" table. To maintain data integrity, you have to do something to the "child" table. Cascading deletes is one thing you can do.

share|improve this answer
Ah, this makes sense. It's all about maintaining integrity! – John Syrinek Jun 20 '13 at 14:52

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.