sql-server - 是否可以创建一个约束来指定子表上的子集不会针对主表上的不同父项重复？

Question

我有下表(表示为 CodeFirst POCO 类)

Public Class JoinTable
   Public Property ParentId as Integer
   Public Property ChildId as Integer
End Class

现在我想提出一个要求，即对于任何 ParentId，关联的 ChildId 应该是唯一的。

因此，记录如
ParentId:1, ChildId: 11
ParentId:1, ChildId: 12
ParentId:2, ChildId: 11

是有效的，但是当我添加 ParentId: 2, ChildId: 12 的记录时，这将无效，因为现在 ParentIds 1 和 2 具有相同的 ChildIds。

如何做到这一点(在 SQLServer LocalDb 上)？可以使用 Entity Framework 来完成吗？

Answer 1

我将此视为“能否在数据库中建立/强制执行这种关系”的挑战，并最终找到了这样做的方法。但是，我强烈建议不要这样做——它会很难看。而且我当然不希望 Entity Framework 能够用它做任何智能的事情。

我们可以做的是显式地对子集进行建模。我们创建一个特殊的空集，然后每个其他集都必须通过添加一个比该集中当前最大元素大的新元素从一个较小的集派生出来。通过添加适当的唯一性约束，我们可以确保每个集合只能以一种方式导出 - 如果每个集合都有一个可选的 Parent , 一个特定集合只能有一个 parent 。

所以这是数据库表:

create table Sets (
    SetID uniqueidentifier not null
        constraint DF_Set_IDs DEFAULT (newsequentialid()),
    SubSetID uniqueidentifier null,
    ParentID int null,
    ElementValue int null,
    SubsetElement int null,
    constraint PK_Set PRIMARY KEY (SetID),
    constraint UQ_Set_XRef UNIQUE (SetID,ElementValue),
    constraint CK_Set_Empty CHECK (
        (SetID = '00000000-0000-0000-0000-000000000000'
             and ElementValue is null and SubSetID is null) or
        (SetID <> '00000000-0000-0000-0000-000000000000'
             and ElementValue is not null and SubSetID is not null)
    ),
    constraint CK_Set_LargerElement CHECK (
        SubsetElement < ElementValue
    ),
    constraint FK_Set_SubSet FOREIGN KEY (SubSetID)
         references Sets (SetID),
    constraint FK_Set_SubSet_XRef FOREIGN KEY (SubSetID,SubsetElement) 
         references Sets (SetID,ElementValue),
    constraint CK_Empty_Subsets CHECK (
        (SubSetID = '00000000-0000-0000-0000-000000000000'
             and SubsetElement is null) or
        (SubSetID <> '00000000-0000-0000-0000-000000000000'
             and SubSetElement is not null)
    ),
    constraint UQ_SubSet_And_Current UNIQUE (SubSetID,ElementValue),
    constraint FK_Set_Parent FOREIGN KEY (ParentID) references Parents (ParentID)
)

然后我们还需要一个过滤的唯一约束以确保父级仅“拥有”一组:

create unique index UQ_OwnedSetParents on Sets (ParentID)
  where ParentID is not null

那么，让我们依次考虑每个约束及其给聚会带来的影响:

• PK_Set - 该表的主键。我已将该列设为 uniqueidentifier主要是为了唯一int此表中出现的 s 与集合中存在的数字相关。

• UQ_Set_XRef - 跨实际主键和 ElementValue 的“ super 键”值，必要时允许外键检查两列。

• CK_Set_Empty - 我们只需要一个空集，所以我们为该行的 SetID 选择一个特殊值然后确保它是唯一具有 NULL 的集合ElementValue并且不基于子集。

• CK_Set_LargerElement - 我们要确保在构建集合时，每个集合的 ElementValue大于它所基于的子集中的最大值。通过归纳，子集中最大的元素是子集的ElementValue。 ，其中更多内容如下。

• FK_Set_SubSet - 将此集合链接到它所基于的子集的外键

• FK_Set_SubSet_XRef - 这个外键确保我们在 SubsetElement 中的值是 ElementValue对于该子集行。

• CK_Empty_Subsets - 需要进行此检查，因为如果其中一列包含 NULL，则不会检查外键引用- 所以我们有这个检查备份 FK_Set_SubSet_XRef并确保正确处理空集。

• UK_SubSet_And_Current - 这确保对于任何给定的子集，最多有一个集合使用特定的元素值建立在它的基础上 - 这是最终使每个集合唯一可表示的原因

• FK_Set_Parent - 在这一点上有点虎头蛇尾 - 该套装可选择由 parent 拥有

• UQ_OwnedSetParents - 这确保每个 parent 最多只“拥有”一个 parent 。

您可能会看到，操作这张表会有些复杂。要枚举集合的成员，您需要访问特定的集合，然后继续访问构建每个集合的子集，直到到达空集。

如果新元素比任何现有元素都大，则向集合中添加新元素很简单。添加一个更小的新元素要复杂得多，尤其是当某些子集由其他父元素拥有时。