提问人:Reece Deyoung 提问时间:10/31/2023 最后编辑:Reece Deyoung 更新时间:10/31/2023 访问量:50
多对多关系中约束的优缺点
Pros and Cons of Constraints across Many-To-Many relationship
问:
我有如下关系的表。
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
我省略了一些与我的问题无关的细节。我的目标是构建表格,以便 中的每个唯一实体都不能链接到具有给定文档类型的多个实体。这是通过上面的约束实现的。SomeOtherTable
LinkedDoc
IX_ManyToMany
虽然它有效,但这个解决方案并不适合我,我想在将数据加载到系统之前立即更改它。主要关注点是对表主键的部分依赖。此外,这意味着我的表格 pk 不是最小的。IX_ManyToMany
ID
LinkedDoc
ROWGUIDCOL
有没有更好的选择?我愿意重构我的表,但我想避免使用数据库触发器和函数,因为在这一点上,我觉得我正在将业务逻辑放在数据库中。我更喜欢将 BL 保留在我的 API 中,在那里它是源代码控制且更易于维护的。
我为多对多关系表找到了这个约束 - 两个相关记录都需要引用相同的依赖记录?第一个答案与我的解决方案类似。第二个使用我愿意考虑的 db 函数,但想看看是否可以通过修改架构来完成。虽然,我可以选择简单地允许数据库违反我的要求,然后在 API 中处理它,而不是使用函数解决方案。
答:
这确实是解决这个问题的标准方法:
..
CONSTRAINT IX_ManyToMany UNIQUE (DocumentTypeID, SomeOtherTable)
..
另请注意,实际上并不需要单独的列,PK 可以仅由 .ManyToMany
ID
(LinkedDocID, SomeOtherTableID)
主要关注点是IX_ManyToMany对表主键具有部分依赖性。此外,LinkedDoc 中的 ID 是 ROWGUIDCOL,这意味着我对表的 pk 不是最小的。
我不确定您为什么要使用 ,这确实适用于复制方案,但它无关紧要。是的,它是一种依赖关系,但您需要考虑有两个键:一个 PK on 和一个辅助 on,而额外的列只是其辅助键的一部分,需要进入外部表。ROWGUIDCOL
LinkedDoc
ID
UNIQUE
(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);
评论
上一个:弱实体与三元关系的双重二元关系
下一个:使 (3) 复合主键的冗余度降低
评论