mysql table multi foreign key cascade
Let's say I have 4 tables related as below diagram
ad --> advertisable <-- advertisable_product --> product
The 3 -> means as below, all of them are OneToOne related
- ad has a FK to advertisable (nullable)
- advertisable_product has a FK to advertisable
- advertisable_product has a FK to product
I have set up on delete cascade constraint on 3.
The question is: When the product is deleted, advertisable_product is deleted automatically by the on delete cascade constraint, but how to make it also delete the advertisable and set the FK in ad to null.
The only way I can figure out is to write a trigger to do that. Is there any better solution or better design to achieve that?
Any advice or comment are welcome, Thanks ~
It's not possible. According to the FK logic there mught be another product tied to the advertisable.
Imagine there is 1 advertisable - "A1" and 2 products "P1" and "P2". advertisable_product has 2 records
Now you delete "P1". The "A1 P1" is deleted by the cascade but "A1" can't be deleted because it's used in the "A1 P2"