PostgreSQL-唯一的行，与顺序无关 [英] PostgreSql - unique rows, independent of order

问题描述

我在当前问题上遇到了一些困难

I'm having a bit of difficulties with the current problem

一个成员与另一个成员匹配-它只能出现

a member has a match with another member - it can only appear once.

说我们有一个方案，其中有一个名为Member的表和另一个名为Match的表

say we have a scheme with a table called Member and another table called Match

   Member
-------------
| id | name |
-------------
| 1  | bob  |
| 2  | tim  |



      Match
------------------------
| memberid | requestid |(memberid is a foreign key to member same as requestid)
-------------------------
| 1        |     2     |

但是现在成员2发出了一个请求，它应该拒绝它，我正在寻找该类型的约束或索引的名称。

however now a request comes in from memberid 2 and it should reject it, I'm trying to find the name for that type of constraint or index. which is unique/match but doesn't matter which column.

所以这应该是无效的

      Match
------------------------
| memberid | requestid |(memberid is a foreign key to member same as requestid)
-------------------------
| 1        |     2     |
| 2        |     1     |

我唯一的想法是添加一个约束，其中 memberid<> requestid 。但我认为可能会有更好的方法

my only thoughts are adding a constraint where memberid <> requestid. But I thought there might a better way

推荐答案

如果您尝试仅使用一对行，则可以使用唯一约束。关键是要在表达式上建立唯一索引：

If you are trying to have only one row for pairs, then you can use a unique constraint. The key is to build a unique index on an expression:

create unique index unq_matches_memberid_requestid
    on matches(least(memberid, requestid), greatest(memberid, requestid));

这篇关于PostgreSQL-唯一的行，与顺序无关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！