CODEWITHSUNDEEP
sqlforeign-keyscascade
Lionel Cichero
Lionel Cichero

Reputation: 563

On delete cascade with 2 different tables

I have the following case, what I would like to do is, if I delete a row from DashboardKpi or DashboardGrid, the corresponding record on ComponentProperty should be deleted as well.

Is this possible? So far, I was only able to do the other way around, if I delete a ComponentProperty, the corresponding DashboardKpi or DashboardGrid gets deleted, but this is definitely not what I want.

ER Diagram

Any suggestion on how can I do?

Upvotes: 1

Views: 122

Answers (1)

Kai
Kai

Reputation: 337

Delete cascade wont help you. As you mentioned when you delete row cascade it will delete all other rows in other tables those referencing to original deleting row, after that your original row.

The reason of logic is that rows in ComponentProperty can be without DashboardKpi or DashboardGrid, but rows in DashboardKpi or DashboardGrid (if they have reference to ComponentProperty) cant not cause they depend on ComponentProperty.

You could solve your problem in different ways depending on your DBMS. Common to most of them is to use procedures or triggers. If you use Postgresql then you can use ON DELETE rule as well.

Upvotes: 2

Related Questions