What is the significance of on delete cascade?
I don't design schemas everyday, but when I do, I try to set up 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?
If you like the Parent and Child terms and you feel they are easy to remember, 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 aggressive) You Can't Kill Parents! Only childless rows can be killed (deleted.)