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

  1. ad has a FK to advertisable (nullable)
  2. advertisable_product has a FK to advertisable
  3. 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 ~

Answers


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

A1 P1

A1 P2

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"


Need Your Help

Calling Java from a Java console app and an ASP.NET app

java asp.net web-services console-application

Recently I got asked to write a java application for my company. I'm a seasoned .Net developer so this is all new ground to me.

Truncated MySQL field when connections from MS Access

mysql ms-access

I have created a linked table to a MySQL table in MS Access 2003. I used the the mysql-connector-odbc-5.1.6-win32 driver I found on the MySQL site. When I view the table I can only see 70 charact...