两个独特的列（任何订单都是唯一的）postgresql [复制]

This question already has an answer here:

这个问题在这里已有答案：

How to create unique index where column order is not taken into account (set?) [duplicate] 1 answer
如何创建不考虑列顺序的唯一索引（设置？）[重复] 1个答案

I have a table which links together two identifiers. I want these to be unique, i.e.:

我有一个表连接两个标识符。我希望这些是独特的，即：

(1, 2)
(1, 3)
(2, 1) <----- invalid, violates 1,2

I tried the following:

我尝试了以下方法：

CREATE TABLE identifiers_relationships (
    id serial primary key,
    identifier1_id integer,
    identifier2_id integer,

    check (
        identifier1_id != identifier2_id
    )
);

CREATE OR REPLACE RULE identifiers_relationships_duplicate_rule AS 
    ON INSERT TO identifiers_relationships 
    WHERE EXISTS 
    (
        SELECT * FROM identifiers_relationships WHERE
        /* basically check for (a, b) already existing) */
        (identifier1_id = NEW.identifier1_id AND identifier2_id = NEW.identifier2_id)
        OR
        /* OR check if (b, a) exists */
        (identifier1_id = NEW.identifier2_id AND identifier2_id = NEW.identifier1_id)
    )
    DO NOTHING;

But then it still lets me do this:

但是它仍然让我这样做：

db=> select * from identifiers_relationships;
 id | identifier1_id | identifier2_id 
----+----------------+----------------
  1 |              2 |              1
  2 |              1 |              2
(2 rows)

2 个解决方案

#1

You can do this by making the first identifier less than the second and putting a unique constraint on the two values:

您可以通过使第一个标识符小于第二个标识符并对这两个值设置唯一约束来执行此操作：

CREATE TABLE identifiers_relationships (
    id serial primary key,
    identifier1_id integer,
    identifier2_id integer,

    check (identifier1_id < identifier2_id),
    unique (identifier1_id, identifier2_id)
);

You can use <= if you want to allow equality.

如果要允许相等，可以使用<=。

#2

You can create a function based index on:

您可以在以下位置创建基于函数的索引：

create index unq_identifiers_relationships 
on identifiers_relationships (   least(identifier1_id,identifier2_id),
                              greatest(identifier1_id,identifier2_id));

... and that will prevent the insertion of (1,2) if (2,1) is already present.

......如果（2,1）已经存在，那将阻止插入（1,2）。

#1