Andrei M. Andrei M. - 3 months ago 7
SQL Question

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.


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 |


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.

so this should be invalid

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


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

Answer

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));