如何在检查约束中引用其他表？

Question

我有一个表，ProductSupportArticles：

ProductSupportArticleID int NOT NULL <primary key>
ParentArticleID int NULL
ProductID int NOT NULL
Title varchar(100) NOT NULL
Content varchar(MAX) NOT NULL

ProductID是Products.ID的外键，ParentArticleID是同一个表ProductSupportArticles.ProductSupportArticleID的外键。我有一个检查约束 ProductSupportArticleID != ParentArticleID ，以便文章不能成为其自己的父项。

但是，与特定产品相关的支持文章不应成为与不同产品相关的文章的父项或子项。如何添加检查约束或类似的说法： (ProductID = (SELECT ProductID FROM ProductSupportArticles P WHERE ParentArticleID = P.ProductSupportArticleID))

或者我应该如何以不同的方式实现我的表？

Answer 1

1. 在 (ProductSupportArticleID, ProductID) 上创建 UNIQUE 约束。
2. 让 FK 引用 (ParentArticleID, ProductID) 到 (ProductSupportArticleID, ProductID)

警告：通过 CHECK 约束中包含的 UDF 强制执行业务规则存在多个漏洞。例如，它们可能会针对多行修改给出误报和漏报。而且它们的速度也很慢。

Answer 2

工作示例

示例表：

create table products (productid int primary key)
insert products select 1
insert products select 2
GO

create table ProductSupportArticles (
ProductSupportArticleID int NOT NULL primary key,
ParentArticleID int NULL references ProductSupportArticles(ProductSupportArticleID),
ProductID int NOT NULL references products (productid),
Title varchar(100) NOT NULL,
Content varchar(MAX) NOT NULL
)
GO

支持函数

create function dbo.getProductSupportArticleParent(@ParentArticleID int)
returns int
with returns null on null input
as
begin
return (select ProductID from ProductSupportArticles where ProductSupportArticleID = @ParentArticleID)
end
GO

约束

alter table ProductSupportArticles add check(
    ParentArticleID is null or
    dbo.getProductSupportArticleParent(ParentArticleID) = ProductID)
GO

测试

insert ProductSupportArticles select 1,null,1,3,4
insert ProductSupportArticles select 2,null,1,3,4
insert ProductSupportArticles select 3,null,2,3,4
insert ProductSupportArticles select 4,1,1,3,4

到目前为止，下一个会破坏它，因为 5 的父级是 1，而 1 属于产品 1。

insert ProductSupportArticles select 5,1,2,3,4

---

编辑

亚历克斯指出了一个有效的缺陷。为了涵盖这种情况，您需要一个 UPDATE 触发器，将对记录的 ProductID 的更改传播到所有子（和后代）记录。这将是一个简单的触发器，因此我不会在此处提供代码。