sql-server - 强制最大子行数

Question

如果数据库有一对典型的“父子”方式的表，是否有办法强制(不使用触发器)每个父项最多只能有四个子项？

所以我们有一个“Parents”表:

create table dbo.Parents (
    ParentID char(2) not null primary key
    /* Yada Yada Yada */
)

以及子表:

create table dbo.Children (
    ChildID char(2) not null primary key,
    ParentID char(2) not null references dbo.Parents (ParentID)
    /* usw */
)

我们填充“Parents”表:

insert into Parents (ParentID) values ('aa'),('bb')

我希望此插入成功:

insert into Children (ChildID,ParentID) values
('a1','aa'),('a2','aa'),('a3','aa')

但是此插入失败:

insert into Children (ChildID,ParentID) values
('b1','bb'),('b2','bb'),('b3','bb'),('b4','bb'),('b5','bb')

Answer 1

有一种方法可以在不使用触发器的情况下做到这一点，但它是丑陋的代码，我不确定我是否会建议在愤怒时使用它。

该技术使用 indexed view来强制执行约束。通常，我非常乐意使用索引 View 来强制执行约束，当然除了使用触发器来强制执行约束之外。但这里的问题是，违反约束时生成的错误消息没有提及任何约束名称 - 您将收到一条错误消息，并且必须知道该错误消息是由“诱杀装置”生成的在您的数据库中。

但为了您的观看乐趣，这里是 View :

create view dbo.DRI_Parent_Child_Maximum4
with schemabinding
as
    select ParentID,COUNT_BIG(*) as Cnt,
           SUM(536870911) as Meaningless
    from dbo.Children
    group by ParentID
go
create unique clustered index IX_DRI_Parent_Child_Maximum4
on dbo.DRI_Parent_Child_Maximum4(ParentID)

以及它为第二次插入生成的错误消息:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

如果不是很明显，这是通过将 4 * 某个魔数(Magic Number)安排为小于 int 可以表示的最大数字来实现的，但 5 * 相同的魔数(Magic Number)更高超过最大值。

因此，如果有 4 行或更少的行，SUM() 就会起作用，但如果您尝试有 5 行或更多行，我们会收到错误。

---

这种技术甚至可以扩展到为每个父级单独配置允许的最大子级数。您可以将最大值存储在 Parents 表中，并且还有一个查找表，对于每个可能的最大值，存储一个可用的魔数(Magic Number)以用于强制执行该最大值。

---

我昨天弄清楚了如何做到这一点，尽管确信有时会问这个问题，但我找不到任何实例，因此出现了新的问题和答案。正如我在这个答案的顶部所说的那样，我不确定我是否真的会在生产代码中执行此操作，或者是否只是将其保留为一个可爱的技巧。