MS SQL - How to deal with multiple cascade paths

Question

I have the following connection in my database

(Example)
Table 1: Car {id, name}
Table 1: CarPart {id, name, CarId}
Table 1: CarPartDealer {id, name, CarId, CarPartId}

Step 1. I have define cascade on the relation between car and car part so that when a car is deleted all the car parts are also deleted.

Step 2. I have define cascade on the relation between car and CarPartDealer so that when a car is deleted all the dealers are also deleted.

Step 3. I have define cascade on the relation between CarPart and CarPartDealer so that when a CarPart is deleted all the dealers are also deleted.

So now I am left with multiple cascade paths. When a car is deleted - its car parts are deleted, its car parts dealers, and again its car parts dealers.

But this is the behaviour I want.
How do I create the two cascade path?
Thanks.

I don't think you need the `CarPartDealer -> Car` foreign key constraint. — ypercubeᵀᴹ, Oct 16 '13 at 15:59
Irrelevant to the question but with the design you have a Dealer can only deal one car part only and no more. This sounds strange. — ypercubeᵀᴹ, Oct 16 '13 at 16:03

score 0 · Answer 1 · answered Oct 16 '13 at 10:47

0

You should remove CarID from CarPartDealer.

CarID can be determined from CarPartID so it is unnecessary.

Then you will only have one casecase path.

answered Oct 16 '13 at 10:47

podiluska

50,950
7
98
104

The `CarID` may be needed. The FK (to Car) is not needed. – ypercubeᵀᴹ Oct 16 '13 at 16:01

MS SQL - How to deal with multiple cascade paths

1 Answers1