我在 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 联系人类型 | 联系人无效 |