Hello,
I have a simple, but very general problem with MySQL using InnoDB tables.
I am sure you experienced folks can give me a solution...
Using foreign keys I want to delete a row in the table T_Child with WHERE
T_Child.Parent_ID=x
when ALL ROWS in the Parent table with ID=x are deleted.
(This behaviour is very useful
when Parent table is a list where T_Parent.ID is the ID column of the list
and hence, x can occur multiple times
in this column.)
As it is now, as soon as ONE row with ID=x in the Parent table is deleted,
all rows in the Child table
WHERE Parent_ID=x are also deleted.
thanks,
Peter
PS: I used ALTER TABLE T_Child ADD CONSTRAINT FOREIGN KEY Parent_ID
REFERENCES T_Parent (ID) ON CASCADE DELETE;
Both tables are InnoDB tables (otherwise nothing happens). I am using MySQL
4.0.16.