是否有任何“反向”信息？ ON DELETE CASCADE选项？ [英] Is there any "reverse" ON DELETE CASCADE option?

问题描述

假设我在SQL Server中具有以下数据库：

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);

如果我删除订单，所有订单项都将被删除（因为在 FK_OrderItem_Order 约束的DELETE CASCADE 上），但是相应的价格（正常价格和折价价格）将永久保留在数据库中。

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.

SQL Server（或通用SQL）中是否有任何选项可以从 Price 表中删除相应的价格？

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

我可以想到一个非常合适的触发器，但是对于这样简单（又常见）的任务来说太麻烦了。我宁愿在我的约束条件下指定一些内容（ FK_OrderItem_Price 和 FK_OrderItem_DiscountedPrice ），基本上说这是一对一的一个关系，如果删除了子级，则删除父级（在这种情况下， Price 是父表）。

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.

推荐答案

简而言之：不。级联只能从父级到子级 ¹ 起作用，反之则不行。

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

可以说有些父母在失去父母后应该被遣散他们的孩子的最后一个，但这不是当前DBMS的实现方式。这样的特殊参照动作必须使用触发器 ² 。

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.

话虽这么说，这种模型有点奇怪。价格不应该与产品 ³ 相关吗？

That being said, this model is a little strange. Shouldn't price be associated with product³?

¹ 在您的情况下，Order和Price都充当OrderItem的父母。

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

² 或批处理作业，它不必立即发生。或将操作隐藏在明确执行该操作的某种API（存储过程，中间层方法）的后面。

² 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.

³ 尽管有订单稳定性问题。

³ Concerns about order stability notwithstanding.

这篇关于是否有任何“反向”信息？ ON DELETE CASCADE选项？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！