我在 SQL 数据库中有以下表格,我想确保不能针对具有不同客户端的 ContactType 和 Matter 创建联系人。我不确定如何创建该约束,甚至不确定如何调用它来查找它。
CREATE TABLE _Clients (
[Id] int IDENTITY NOT NULL,
[Name] nvarchar(50) NOT NULL
CONSTRAINT [PK__Clients] PRIMARY KEY CLUSTERED (Id ASC))
CREATE TABLE _Matters (
[Id] int IDENTITY NOT NULL,
[ClientId] int NOT NULL,
[Name] nvarchar(50) NOT NULL
CONSTRAINT [PK__Matters] PRIMARY KEY CLUSTERED (Id ASC))
CREATE TABLE _ContactTypes (
[Id] int IDENTITY NOT NULL,
[ClientId] int NOT NULL,
[Name] nvarchar(50) NOT NULL
CONSTRAINT [PK__ContactTypes] PRIMARY KEY CLUSTERED (Id ASC))
CREATE TABLE _Contacts (
[Id] int IDENTITY NOT NULL,
[MatterId] int NOT NULL,
[ContactTypeId] int NOT NULL,
[Name] nvarchar(50) NOT NULL
CONSTRAINT [PK__Contacts] PRIMARY KEY CLUSTERED (Id ASC))
ALTER TABLE _Matters ADD CONSTRAINT [FK__Matters_Clients] FOREIGN KEY([ClientId])
REFERENCES _Clients ([Id])
ALTER TABLE _ContactTypes ADD CONSTRAINT [FK__ContactTypes_Clients] FOREIGN KEY([ClientId])
REFERENCES _Clients ([Id])
ALTER TABLE _Contacts ADD CONSTRAINT [FK__Contacts_ContactTypes] FOREIGN KEY ([ContactTypeId])
REFERENCES _ContactTypes ([Id])
ALTER TABLE _Contacts ADD CONSTRAINT [FK__Contacts_Matters] FOREIGN KEY([MatterId])
REFERENCES _Matters ([Id])
INSERT INTO _Clients VALUES ('Client 1'), ('Client 2')
INSERT INTO _Matters VALUES (1, 'Matter 1.1'), (2, 'Matter 2.1')
INSERT INTO _ContactTypes VALUES (1, 'Client 1 Contact Type'), (2, 'Client 2 Contact Type')
INSERT INTO _Contacts VALUES (1, 2, 'Invalid Contact')
SELECT cl1.[Name] AS MatterClient, cl2.[Name] AS ContactTypeClient, m.[Name] AS Matter, ct.[Name] AS ContactType, c.[Name] AS Contact
FROM _Contacts c
JOIN _Matters m ON c.MatterId = m.Id
JOIN _Clients cl1 ON m.ClientId = cl1.Id
JOIN _ContactTypes ct ON c.ContactTypeId = ct.Id
JOIN _Clients cl2 ON ct.ClientId = cl2.Id
物质客户端 | 联系人类型客户端 | 事情 | 接触类型 | 接触 |
---|---|---|---|---|
客户 1 | 客户 2 | 事项 1.1 | 客户 2 联系人类型 | 联系人无效 |
一种解决方案是将表中的
MatterId
和ContactTypeId
表外_Contacts
以及表ClientId
中的表规范化为_ContactTypes
链接_Matters
表,该链接表还包括ContactId
如下内容:然后,您会将表中的每个字段外键
ContactMatterContactTypeClients
到它们适当的源表。然后隐含地通过设计,不可能有不同的
ContactId
aMatterId
和组合。ContactTypeId
ClientIds
请随意为链接表选择一个更好的名称。我不擅长命名事物。
您可以添加
ClientId
到Contact
表中,然后在其上创建外键。您还需要在其他表上添加辅助唯一约束才能拥有这样的外键。
现在每个都
Contact
必须针对单个ClientId
,并且外键引用相同ClientId
。数据库<>小提琴
如果出于某种原因您不想修改表,您可以使用一个鲜为人知的索引视图 hack 来强制执行多表约束。
首先创建一个至少包含两行的表。
然后创建一个选择无效行的视图,并交叉连接该表。
然后索引它
任何无效行都将使该唯一索引失败。请注意,索引视图始终包含零行并且不占用任何空间。此外,
MatterClientId
和ContactTypeClient
是不必要的,但对于在错误消息中显示很有用。数据库<>小提琴