Delete cascade in many to many relation

[avihaimar]

Hey,

I have table A and B that has many to many relation (Table C is the many to many)

I want that when i delete records from A, the records from C and B will also delete.

I set on delete cascade in C table FK (for A and B)

but when i delete records from A it delete only A,C.

Is there any way to delete A,B,C when i delete records from A???

I know that the problem is that this is many to many , so there is an option that some records from B will be delete despite the fact that they have also relation with records that has not deleted, but i dont care to delete those records.

Thank you.

 

[r937]

table C is a "child" of both A and B

ON DELETE CASCADE only works from parent to child

if you delete a row from A, all related C rows will also be deleted

however, this cannot delete rows in B, because B is not a child of C, it's a parent

r937.com | rudy.ca

 

[avihaimar]

I know this.

I am asking for any idea that can help me in this issue.

if i delete from A i dont want that the related records will exists in B (There is no option that this records in B are retlated to records in A that were not deleted)

 

[TonyGroves]

It sounds you would want to get rid of the ON DELETE CASCADE and just delete from A and B first (using the technique shown in another recent thread), then delete from C.

If that doesn't make sense, can you show us your table structures ("SHOW CREATE TABLE a" etc.)?