如何对表中的两列创建一个聚集索引

Question

我有一张桌子

UserProgrammingLanguagesId
UserId,
Name,
ProgrammingLanguageId,
KnowledgeId.

我插入到ProgrammingLanguageId和KnowledgeId的值是FK-PK relationship.But我不想要的，例如，如果一个用户有ProgrammingLanguageId - 1(Java Script)，他的知识是KnowledgeId -2(中级)，在该表中有相同的值。我想当一些用户有2个值的1时，以防止重复，因为一个用户可以用一个知识id知道一种编程语言，而不是两个或相反的。

这是上传的带有表格https://ibb.co/sPn5zT7的屏幕截图

//i am preventing this and it is working
insert into userPLs values(5,'9d313e61-8fb7-4011-88f0-6494621f43f7','Doe',1,1)

//How can i prevent this insert here?
insert into userPLs values(5,'9d313e61-8fb7-4011-88f0-6494621f43f7','Doe',1,2)

Answer 1

有两种方法可以做到这一点，具体取决于数据库。

一种是在两个值上强制排序，然后有一个唯一的约束：

alter table userPLs add constraint chk_userPLS_ids
    check (ProgrammingLanguageId < KnowledgeId);

alter table userPLs add constraint unq_userPLS_ids
    unique (ProgrammingLanguageId, KnowledgeId);

这将只允许您插入(1，2)，而不是(2，1)，因此您无法获得重复项。

第二种方法是对唯一索引使用least()/greatest()或类似的逻辑：

create unique index unq_userPLS_ids on
    userPLs ( least(ProgrammingLanguageId, KnowledgeId),
             greatest(ProgrammingLanguageId, KnowledgeId),
           );

并非所有数据库都支持least()和greatest()，但它们是大多数数据库中的常见函数。有些数据库不支持基于函数的索引，但通常有一种变通办法。

在SQL Server中，您需要使用计算列和case表达式：

alter table userPLs add column least_pkid as (case when ProgrammingLanguageId < KnowledgeId then ProgrammingLanguageId else KnowledgeId end)持久化；

alter table userPLs add column greatest_pkid as (case when ProgrammingLanguageId > KnowledgeId then ProgrammingLanguageId else KnowledgeId end)持久化；

create unique index unq_userPLS_ids on
    userPLs ( least_pk, greatest_pk );

我应该补充说，您也可以为此目的定义触发器，但这不是必需的。