Delete trigger as replacement for "ON DELETE CASCADE" to avoid "multiple cascade paths"?

Question

The following table definition:

   CREATE TABLE Customers( id INT NOT NULL PRIMARY KEY, name [varchar](50) )

    CREATE TABLE Orders ( id INT NOT NULL PRIMARY KEY, 
                          customer INT FOREIGN KEY 
                          REFERENCES Customers(id) ON DELETE CASCADE )

    CREATE TABLE OrderDetails ( id INT NOT NULL PRIMARY KEY, 
                                order INT FOREIGN KEY REFERENCES Orders(id) ON DELETE CASCADE, 
                                customer INT FOREIGN KEY REFERENCES Customers(id) ON DELETE CASCADE  )

isn't possible in sql server because there are multiple cascade paths.

I thought let's create OrderDetails without the ON DELETE CASCADE on column order and let's see whether it is possible to enforce referential integrity when deleting an order with a trigger containing:

DELETE FROM OrderDetails
    FROM Deleted d
    INNER JOIN OrderDetails od
    ON od.order = d.id

The trigger fires after the delete in Orders, so it is not possible (The DELETE statement conflicted with the REFERENCE constraint).

I believe the problem lies in the model design and the reference from OrderDetails to Customers is a bad design. However otherwise it would be possible to create OrderDetails for Orders that belong to different Customers.

Two questions:

• what is the best model design?
• is it nevertheless possible to use a trigger?

EDIT: I removed the reference from OrderDetails to Customers, it doesn't make any sense. This resolves all issues.

Answer 1

I would avoid this issue by not putting Customer in OrderDetails at all, as it is derivable from joining on Orders.

As it stands even with the Foreign Key there is nothing preventing the Customer in OrderDetails being different from the one in Orders.

Additionally do you really want a cascading delete for this anyway? Presumably the business will want some record of historic orders.

Answer 2

For sure, it is not correct and does not make sense to have the CustomerId in the OrderDetails. This would give you a kind of transitive dependency.
Additionally - depending on your real model - one should never be allowed to delete a Customer if any Order relates to it. You should rather plan either a Boolean, or beter a Date field like DeadCustomer :/

For the structure:
Clients: Id, Name, etc
Order: OrderId, OrderDate, CustID...
OrderDetails: OrderId, ProductId, Quant, UnitPrice...
Products: ProductId, Description, Status, UnitPrice...