如何设计一个表，其中它是自引用的和顺序不重要的

Question

假设我有一个用户列表，我想记录他们之间的握手：

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    name VARCHAR(70)
);

CREATE TABLE hand_shaking (
    user1 INTEGER NOT NULL REFERENCES user,
    user2 INTEGER NOT NULL REFERENCES user
);

user表的数据如下：

1,Sarah
2,John
3,Alex

hand_shaking表可以有如下所示的数据：

1,2

也就是说莎拉和约翰握手了。

现在的问题是，我应该如何设计hand_shaking表，以便不允许重复？例如，如果1,2已经插入，2,1就会失败。

我正在使用postgresql，我试图在hand_shaking表上声明hand_shaking，但是在插入1,2之后仍然允许使用2,1。

Answer 1

为两个不同的组合创建索引相同值的唯一索引：

create unique index only_one_handshake 
    on hand_shaking (least(user1,user2), greatest(user1,user2));

Answer 2

您可以使用数组构建检查逻辑，例如：

t=# create table so27 (a text,b text, c text);
CREATE TABLE
t=# insert into so27 select 'a','b','c';
INSERT 0 1
t=# insert into so27 select 'a','b','d';
INSERT 0 1
t=# insert into so27 select 'c','a','b';
INSERT 0 1
t=# select *,'{b,c,a}'::text[] @> ('{}'::text[]||a||b||c)::text[] from so27;
 a | b | c | ?column?
---+---+---+----------
 a | b | c | t
 a | b | d | f
 c | a | b | t
(3 rows)