Is there any "reverse" ON DELETE CASCADE option?

Question

Let's say I have the following database in SQL Server:

CREATE TABLE [Order]
(
  ID BIGINT IDENTITY(1,1)
  CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (ID)
);

CREATE TABLE OrderItem
(
  ID BIGINT IDENTITY(1,1),
  ORDER_ID BIGINT NOT NULL,
  PRICE_ID BIGINT NOT NULL,
  DISCOUNTED_PRICE_ID BIGINT NULL,
  CONSTRAINT PK_OrderItem PRIMARY KEY CLUSTERED (ID)
);

CREATE TABLE Price
(
  ID BIGINT IDENTITY(1,1),
  AMOUNT FLOAT NOT NULL,
  CURRENCY VARCHAR(3) NOT NULL,
  CONSTRAINT PK_Price PRIMARY KEY CLUSTERED (ID)
);

ALTER TABLE OrderItem ADD CONSTRAINT FK_OrderItem_Order
FOREIGN KEY (ORDER_ID) REFERENCES [Order](ID) ON DELETE CASCADE;

ALTER TABLE OrderItem ADD CONSTRAINT FK_OrderItem_Price
FOREIGN KEY (PRICE_ID) REFERENCES Price(ID);

ALTER TABLE OrderItem ADD CONSTRAINT FK_OrderItem_DiscountedPrice
FOREIGN KEY (DISCOUNTED_PRICE_ID) REFERENCES Price(ID);

If I delete an order, all order items will be deleted (because of ON DELETE CASCADE on FK_OrderItem_Order constraint), but corresponding prices (normal and discounted) will remain in the database forever.

Is there any option in SQL Server (or generic SQL) to delete corresponding prices from Price table?

I can think of a trigger which is a perfect match, but it is too much hassle for such simple (and common) task. I would prefer to specify something on my constraints (FK_OrderItem_Price and FK_OrderItem_DiscountedPrice) that basically say "this is one-to-one relationship", delete parent (Price is a parent table in this case) if a child was deleted.

Answer 1

In a nutshell: no. Cascading works only from parent to child¹, not the other way around.

It could be argued that some parents should be removed when they lose the last of their children, but that's simply not how current DBMSes are implemented.

You'll have to use a trigger for such "special" referential action, or a batch job it it doesn't have to happen immediately. Or hide the operations behind some sort of API (stored procedure, middle-tier method) that does that explicitly.

See also: order stability.

---

¹ In you case, Order and Price both act as parents to OrderItem.

Answer 2

Add the OrderItemID to Price and set up a cascade delete relationship. This column is of course redundant but it allows you to have a cascade delete in the right direction.

Consider inlining the Price table two times into OrderItems. As this is a 1:1 relationship you can do that. It is a matter of taste whether you like this solution or not.