sql - 如何在字段顺序无关紧要的情况下实现唯一性

Question

我认为下面的例子最能说明这种情况。假设我们有以下表结构:

-------------------------------------
Member1   int      NOT NULL (FK)(PK)
Member2   int      NOT NULL (FK)(PK)
-------------------------------------
Statust   char(1)  NOT NULL

以下是表格的内容:

Member1    Member2    Status
----------------------------
  100        105        A

我的问题是如何实现唯一性，以便以下 INSERT 语句基于表中已有的一行失败。

INSERT status_table (Member1,Member2,Status) VALUES(105,100,'D');

基本上，我正在尝试对两个成员之间的关系进行建模。无论我们是 (100,105) 还是 (105,100)，状态字段都是相同的。

我知道我可以使用 before_insert 和 before_update 触发器来检查表中的内容。但我想知道是否有更好的方法来做到这一点...如果我的数据库模型不同...

Answer 1

如果您可以确保所有应用程序/用户都按照从小到大的顺序存储成员 ID(最小的 MemberID 存储在 Member1 中，最大的存储在 Member2 中) )，那么您可以简单地添加一个检查约束:

ALTER TABLE Status_table
  ADD CONSTRAINT Status_table_Prevent_double_pairs
    CHECK (Member1 < Member2)

如果您不想这样做，或者您希望存储额外的信息(因为您正在存储(只是一个示例)“成员(member) 100 已邀请(点赞、杀死等)成员(member) 150” 而不是反之亦然)，那么你可以使用 @Tegiri 的方法，稍作修改(将两个足够大的整数相乘否则会出现溢出问题):

CREATE TABLE Status_table
( Member1 INT NOT NULL
, Member2 INT NOT NULL
, Status CHAR(1) NOT NULL
, MemberOne  AS CASE WHEN Member1 < Member2 THEN Member1 ELSE Member2 END
          --- a computed column
, MemberTwo  AS CASE WHEN Member1 < Member2 THEN Member2 ELSE Member1 END
          --- and another one
, PRIMARY KEY (Member1, Member2)
, UNIQUE (MemberOne, MemberTwo)
, ...                                    --- FOREIGN KEY details, etc 
) ;