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.

Branko Dimitrijevic · Answer 1 · 2021-08-10T05:55:29.857

13

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.

edited Aug 10 '21 at 05:55

answered Jul 12 '14 at 12:52

Branko Dimitrijevic

50,809
10
93
167

For those coming here using MySQL and thinking about implementing a trigger now, keep in mind that in MySQL triggers don't fire on cascaded actions: https://stackoverflow.com/a/6095593/1813669 – Scadge Aug 23 '18 at 15:24
Parent-Child relationship does not work with 1-1 relationship such as Entity<=>Settings. If each Entity is required to have a unique Setting, it requires a settings_id foreign key on the Entity. But the setting should be deleted once the entity is deleted. And you shouldn't be able to delete Setting alone. In this case it requires an opposite way Cascade and Restrict. Any suggestions? – Guney Ozsan Apr 13 '19 at 12:22
@GuneyOzsan 1:1 relationship is usually represented as a single table. In your example, both Entity and Settings fields would be in the same table. – Branko Dimitrijevic Apr 13 '19 at 12:41

score 5 · Answer 2 · answered Oct 16 '18 at 09:05

5

you can create a trigger which acts as a reverse cascade:

DELIMITER $$
CREATE TRIGGER reverse_cascade_OrderItem_Price
AFTER DELETE ON `OrderItem`
FOR EACH ROW
BEGIN
    DELETE FROM `Price` WHERE ID = old.PRICE_ID;
END$$

answered Oct 16 '18 at 09:05

Liran Brimer

3,418
1
28
23

score 4 · Answer 3 · answered Jul 11 '14 at 15:24

4

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.

answered Jul 11 '14 at 15:24

usr

168,620
35
240
369

I've got only to columns in my example - inlining might be fine. But what if I have 10 columns? – AndreyR Jul 11 '14 at 15:39
Adding additional column is an interesting option but it will increase since a) Price is going to be most populated table b) an index will probably be required on this column to make deletions fast – AndreyR Jul 11 '14 at 15:43
@AndreyR correct. These are the only automatic deletion options I know of. If you don't like those I believe there is no other answer than a trigger of app change. Or, garbage-collect unused price rows nightly. – usr Jul 11 '14 at 15:54

Is there any "reverse" ON DELETE CASCADE option?

3 Answers3

Linked