CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Andrei
Andrei

Reputation: 1216

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

Upvotes: 3

Views: 968

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 4

Related Questions