PostgreSQL强制执行列的唯一双向组合 [英] Postgresql enforce unique two-way combination of columns

问题描述

我正在尝试创建一个表，该表将在两个方向上强制执行相同类型的两列的唯一组合。例如。这将是非法的：

I'm trying to create a table that would enforce a unique combination of two columns of the same type - in both directions. E.g. this would be illegal:

col1 col2
   1    2
   2    1

我想出了这个，但它不起作用：

I have come up with this, but it doesn't work:

database=> \d+ friend;
                                Table "public.friend"
    Column    |           Type           | Modifiers | Storage  | Stats target | Description
--------------+--------------------------+-----------+----------+--------------+-------------
 user_id_from | text                     | not null  | extended |              |
 user_id_to   | text                     | not null  | extended |              |
 status       | text                     | not null  | extended |              |
 sent         | timestamp with time zone | not null  | plain    |              |
 updated      | timestamp with time zone |           | plain    |              |
Indexes:
    "friend_pkey" PRIMARY KEY, btree (user_id_from, user_id_to)
    "friend_user_id_to_user_id_from_key" UNIQUE CONSTRAINT, btree (user_id_to, user_id_from)
Foreign-key constraints:
    "friend_status_fkey" FOREIGN KEY (status) REFERENCES friend_status(name)
    "friend_user_id_from_fkey" FOREIGN KEY (user_id_from) REFERENCES user_account(login)
    "friend_user_id_to_fkey" FOREIGN KEY (user_id_to) REFERENCES user_account(login)
Has OIDs: no

是否可以使用约束在没有触发器或任何高级魔术的情况下编写此代码

Is it possible to write this without triggers or any advanced magic, using constraints only?

推荐答案

不需要扩展的Neil解决方案的一种变化是：

A variation on Neil's solution which doesn't need an extension is:

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

Neil的解决方案允许您使用任意数量的列。

Neil's solution lets you use an arbitrary number of columns though.

我们都依靠使用表达式来构建索引，该记录已记录在文档中。
https://www.postgresql.org/docs/current/indexes-expressional.html

We're both relying on using expressions to build the index which is documented https://www.postgresql.org/docs/current/indexes-expressional.html

这篇关于PostgreSQL强制执行列的唯一双向组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！