SQL Server:两列的单个值的唯一索引(！！！非组合)

Question

我有一个团队表，每个团队有两个代码。团队成员的代码和团队负责人的代码。

TeamId    Name     MemberCode    LeaderCode
--------------------------------------------
1         Team1    CodeXY        CodeXYZ
2         Team2    CodeAB        CodeBC
...

有两个唯一索引，一个在MemberCode上，另一个在LeaderCode上，确保MemberCodes和LeaderCodes是唯一的。

但是，我如何定义不仅MemberCodes本身是unqiue，而且MemberCodes和LeaderCodes？

MemberCode不应为LeaderCode。

有人有主意吗？

附言：不能在Create Unique index UIDX_12 On tbl (MemberCode, LeaderCode)这样的两列上使用唯一索引！

Answer 1

使用这种数据结构，我认为您必须有一个触发器。

您可以重新格式化数据，这样您就有了一个表和（至少）三列：

• 工作组ID
• 代码
• 代码类型

然后您可以添加约束：

• codetype仅为"成员"或"领导者"
• code是唯一的
• teamid在teamid表中
• teamid/codetype是唯一的

这将允许您为每个团队存储这些值中的每一个（假设这些值不是NULL）。

在createtable语句中，这可能看起来像：

create table . . .
    check codetype in ('member', 'leader'),
    unique(code),
    teamid references teams(teamid),
    unique (teamid, codetype)
    . . .

Answer 2

您可以使用索引视图强制执行此约束。类似于：

create table dbo.MColumnUnique (
    MemberName int not null,
    LeaderName int not null
)
go
create table dbo.Two (ID int not null primary key,constraint CK_Two_ID CHECK (ID in (1,2)))
go
insert into dbo.Two(ID) values (1),(2)
go
create view dbo.MColumnUnique_Enforcer (Name)
with schemabinding
as
    select
        CASE WHEN ID = 1 THEN MemberName ELSE LeaderName END
    from
        dbo.MColumnUnique
            cross join
        dbo.Two
go
create unique clustered index IX_MColumnUnique_Enforcer on dbo.MColumnUnique_Enforcer (Name)
go
insert into dbo.MColumnUnique (MemberName,LeaderName) values (1,2),(3,4) --Works
go
insert into dbo.MColumnUnique (MemberName,LeaderName) values (4,5) --Fails
go
insert into dbo.MColumnUnique (MemberName,LeaderName) values (6,6) --Fails

希望你能看到我上面的结构和你的表格之间的相似之处。

dbo.Two只是一个通常有用的帮助表，它正好包含两行，用于将数据执行有限的取消透视操作，使其成为一列。

Answer 3

您可以使用触发器来完成，但我会使用CHECK CONSTRAINT。

创建一个函数，该函数接受varchar参数（或MemberCode和LeaderCode使用的任何数据类型），并返回一位：如果没有与参数值匹配的LeaderCode或MemberCode，则返回0；如果匹配，则返回1。

然后在表上设置一个检查约束，指定：

• 成员代码<>LeaderCode AND
• YourFunction（MemberCode）=0 AND
• YourFunction（LeaderCode）=0

编辑基于Damien的评论：

为了防止函数包含您刚刚添加的行，您还需要传递[code]列（您说它是UNIQUE），而不计算[code]具有该值的行。