Is there any "reverse" ON DELETE CASCADE option?
database, database-design, sql, sql-server
Solution
In a nutshell: no. Cascading works only from parent to child1, 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.
1 In you case, Order and Price both act as parents to OrderItem.
Problem
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.