sql - 关于 ON DELETE SET DEFAULT 的问题。默认值是否需要存在于引用的表中？

Question

假设我有下表：

CREATE TABLE Products
(
    ProdID INT PRIMARY KEY IDENTITY(100,5),
    ProdName VARCHAR(20)
)

然后我插入一些行：

INSERT INTO Products VALUES ('Coat Rack') --Will be given a ProdID of 100
INSERT INTO Products VALUES ('Coffee Table') --Will be given a ProdID of 105

然后我创建另一个名为 Orders 的表，该表具有 FK 约束：

CREATE TABLE Orders
(
   OrderID INT PRIMARY KEY IDENTITY(800,2),
   ProductID INT DEFAULT 0,
   CONSTRAINT fk_ProdID FOREIGN KEY(ProductID) REFERENCES Products(ProdID) ON DELETE SET DEFAULT
)

请注意，ProductID 列的默认值为 0，以及指定 ON DELETE SET DEFAULT 设置的 FK 约束。

然后插入一行：

INSERT INTO Orders VALUES (105) --Row references the "Coffee Table" product. 

如果我尝试从 Products 表中删除产品“Coffee Table”，我会收到一条消息，指出无法删除该产品，因为它在 Orders 表中被引用。我理解是这样，但我期待 FK 约束只是允许删除该行，然后将 0 放在引用行中。0 当然是引用列 (ProductID) 的默认值，并且 FK 约束指定 ON DELETE SET DEFAULT。

那么使用 ON DELETE SET DEFAULT，默认值是否还需要存在于引用的表中？

如果是这样的话，我觉得有点奇怪。有人可能想在引用的表中创建一个“虚拟”行，并将默认值设置为等于该虚拟行使用的任何 ID。我们这样做是为了如果我们删除一个产品，任何引用行都将指向该虚拟产品而不是实际产品

标签： sqlsql-serverdatabasetsql

Answer 1

根据文档

默认设置

当删除父表中的相应行时，构成外键的所有值都设置为其默认值。要执行此约束，所有外键列都必须具有默认定义。如果列可以为空并且没有设置显式默认值，则 NULL 将成为该列的隐式默认值。

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql?view=sql-server-ver15

它确实不能很好地解释的是默认值必须存在于父表中。如果不是，你得到约束违反的错误。

根据您的示例向您展示这一点的方法

CREATE TABLE Products
(
    ProdID INT PRIMARY KEY IDENTITY(100,5),
    ProdName VARCHAR(20)
)

CREATE TABLE Orders
(
   OrderID INT PRIMARY KEY IDENTITY(800,2),
   ProductID INT DEFAULT 0,
   CONSTRAINT fk_ProdID FOREIGN KEY(ProductID) REFERENCES Products(ProdID) 
   ON DELETE SET DEFAULT
)

INSERT INTO Products VALUES ('Coat Rack') --Will be given a ProdID of 100
INSERT INTO Products VALUES ('Coffee Table') --Will be given a ProdID of 105

SET IDENTITY_INSERT Products ON; -- Enable to insert default dummy product 
INSERT INTO Products (ProdID, ProdName) VALUES ( 0 , 'Dummy') -- Insert dummy product

INSERT INTO Orders VALUES (105) --Row references the "Coffee Table" product. 

DELETE FROM Orders where ProductID = 105

dbfiddle 中代码的完整演示

db<>小提琴