SQL Server Management Studio - 复合主键

I've a table (SQL Server 2008) with a composite primary key. How can I prevent this behaviour?:

我有一个带有复合主键的表(SQL Server 2008)。我该如何防止这种行为?:

insert into myTable values ('A','B')
insert into myTable values ('B','A')

I tried with UNIQUE restriction and WITH_IGNORE_DUP_KEYS but I can't solve it.

我尝试使用UNIQUE限制和WITH_IGNORE_DUP_KEYS,但我无法解决它。

Thanks in advance!

提前致谢!

2 个解决方案

#1

As SQL Server does not allow a function based index, one solution is to force inserting the values such that the "smaller" one is always stored in the first column:

由于SQL Server不允许基于函数的索引,因此一种解决方案是强制插入值,使“较小”的值始终存储在第一列中:

alter table myTable add constraint chk_pk 
   check (col1_1 <= col_2);

If you don't like this (because you don't want to force a special "ordering" of the values during insert), you need to define two computed columns and create a unique index on them:

如果您不喜欢这样(因为您不想在插入期间强制对值进行特殊的“排序”),则需要定义两个计算列并在其上创建唯一索引:

ALTER TABLE myTable ADD 
    min_pk AS case when col_1 < col_2 then col_1 else col_2 end,
    max_pk AS case when col_1 > col_2 then col_1 else col_2 end;

CREATE UNIQUE INDEX idx_unique_pk ON myTable (min_pk, max_pk);

#2

i think before insert you can check if combination 'B' and 'A' exists in both column

我认为在插入之前你可以检查两列中是否存在组合'B'和'A'

 Declare @t1 table(col1 varchar(2),col2 varchar(2))
insert into @t1 values('A','B')

if not exists(select col1 from @t1 where ((col1='B' or Col1='A') and (col2='B' or Col2='A')))
insert into @t1 values('B','A')

select * from @t1

or you can define constraint.

或者你可以定义约束。

Thanks for good question .

谢谢你的好问题。

#1