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 -
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?