多对多关系中约束的优缺点

Pros and Cons of Constraints across Many-To-Many relationship

提问人:Reece Deyoung 提问时间:10/31/2023 最后编辑:Reece Deyoung 更新时间:10/31/2023 访问量:50

问:

我有如下关系的表。

CREATE TABLE DocumentType
(
   ID uniqueidentifier primary key,
   ...
)
GO

CREATE TABLE LinkedDoc
(
   ID uniqueidentifier ROWGUIDCOL NOT NULL,
   DocumentTypeID uniqueidentifier NOT NULL,
   ...,
   CONSTRAINT [PK_LinkedDoc] 
       PRIMARY KEY CLUSTERED (ID, DocumentTypeID)
)
GO

ALTER TABLE LinkedDoc 
    ADD CONSTRAINT FK_DocumentType_LinkedDoc 
        FOREIGN KEY(DocumentTypeID) REFERENCES DocumentType (ID)
GO

CREATE TABLE SomeOtherTable
(
   ID uniqueidentifier primary key,
   ...
)

CREATE TABLE ManyToMany 
(
   ID uniqueidentifier primary key,
   LinkedDocID uniqueidentifier,
   DocumentTypeID uniqueidentifier
   SomeOtherTableID uniqueidentifier,

   CONSTRAINT IX_ManyToMany UNIQUE (DocumentTypeID, SomeOtherTable)
)
GO

ALTER TABLE ManyToMany WITH CHECK 
   ADD CONSTRAINT FK_SomeOtherTable_ManyToMany 
      FOREIGN KEY (SomeOtherTableID) REFERENCES SomeOtherTable (ID)
GO

ALTER TABLE ManyToMany WITH CHECK 
    ADD CONSTRAINT FK_LinkedDoc_ManyToMany 
        FOREIGN KEY (LinkedDocID, DocumentTypeID) REFERENCES LinkedDoc (ID, DocumentTypeID)
GO

我省略了一些与我的问题无关的细节。我的目标是构建表格,以便 中的每个唯一实体都不能链接到具有给定文档类型的多个实体。这是通过上面的约束实现的。SomeOtherTableLinkedDocIX_ManyToMany

虽然它有效,但这个解决方案并不适合我,我想在将数据加载到系统之前立即更改它。主要关注点是对表主键的部分依赖。此外,这意味着我的表格 pk 不是最小的。IX_ManyToManyIDLinkedDocROWGUIDCOL

有没有更好的选择?我愿意重构我的表,但我想避免使用数据库触发器和函数,因为在这一点上,我觉得我正在将业务逻辑放在数据库中。我更喜欢将 BL 保留在我的 API 中,在那里它是源代码控制且更易于维护的。

为多对多关系表找到了这个约束 - 两个相关记录都需要引用相同的依赖记录?第一个答案与我的解决方案类似。第二个使用我愿意考虑的 db 函数,但想看看是否可以通过修改架构来完成。虽然,我可以选择简单地允许数据库违反我的要求,然后在 API 中处理它,而不是使用函数解决方案。

SQL-Server 对多 RDBMS 数据库规范化

评论


答:

1赞 Charlieface 10/31/2023 #1

这确实是解决这个问题的标准方法:

..
   CONSTRAINT IX_ManyToMany UNIQUE (DocumentTypeID, SomeOtherTable)
..

另请注意,实际上并不需要单独的列,PK 可以仅由 .ManyToManyID(LinkedDocID, SomeOtherTableID)

主要关注点是IX_ManyToMany对表主键具有部分依赖性。此外,LinkedDoc 中的 ID 是 ROWGUIDCOL,这意味着我对表的 pk 不是最小的。
我不确定您为什么要使用 ,这确实适用于复制方案,但它无关紧要。是的,它是一种依赖关系,但您需要考虑有两个键:一个 PK on 和一个辅助 on,而额外的列只是其辅助键的一部分,需要进入外部表。
ROWGUIDCOLLinkedDocIDUNIQUE(ID, DocumentTypeID)

所以定义应该是

CREATE TABLE LinkedDoc
(
   ID uniqueidentifier NOT NULL,
   DocumentTypeID uniqueidentifier NOT NULL,
   ...,
   CONSTRAINT [PK_LinkedDoc] 
       PRIMARY KEY CLUSTERED (ID),
   INDEX UK_LinkedDoc_DocumentTypeID
       UNIQUE (ID, DocumentTypeID),
   CONSTRAINT FK_DocumentType_LinkedDoc
       FOREIGN KEY(DocumentTypeID) REFERENCES DocumentType (ID)

注意:我使用了唯一索引而不是唯一约束,因为这允许您添加 ,并且仍然可以用作 FK 目标。INCLUDE


另一种选择是使用索引视图强制执行此操作。不过,这通常是为了更有效率,除非您仍然需要索引视图。

CREATE TABLE ManyToMany 
(
   LinkedDocID uniqueidentifier,
   SomeOtherTableID uniqueidentifier,
   PRIMARY KEY PK_ManyToMany (LinkedDocID, SomeOtherTableID),
   CONSTRAINT FK_SomeOtherTable_ManyToMany 
      FOREIGN KEY (SomeOtherTableID) REFERENCES SomeOtherTable (ID)
   CONSTRAINT FK_LinkedDoc_ManyToMany 
      FOREIGN KEY (LinkedDocID) REFERENCES LinkedDoc (ID)
)

然后创建一个包含DocumentTypeID

CREATE VIEW dbo.ManyToMany_DocumentTypeID
WITH SCHEMABINDING
AS

SELECT
  mm.LinkedDocID 
  mm.SomeOtherTableID,
  ld.DocumentTypeID
FROM dbo.ManyToMany mm
JOIN dbo.LinkedDoc ld ON ld.ID = mm.LinkedDocID;

并为其编制索引

CREATE UNIQUE CLUSTERED INDEX IX ON ManyToMany_DocumentTypeID (SomeOtherTableID, DocumentTypeID);

评论

0赞 Reece Deyoung 10/31/2023
谢谢。关于视图解决方案。除了性能之外,它是否具有任何优势,例如在读取/修改数据时避免异常?
1赞 Charlieface 10/31/2023
否,因为外键也会强制执行它。FK 版本不会像视图版本那样受到写入异常(表之间的差异)的影响,因为 FK 强制不会发生写入异常。我认为优点/缺点基本上可以归结为:由于额外的列,FK 在插入/更新方面更烦人,由于必须使用连接维护完整的索引视图索引,视图性能较低。