I have two tables: a user
table and a friendship
table. Say the friendship
table looks like this:
friendship
------------
user_one_id
user_two_id
other_fields
I need to enforce the uniqueness of the combination of values of (user_one_id, user_two_id)
and disregard the ordering, so:
user_one_id | user_two_id
------------+------------
1 | 2
2 | 1 -- the DBMS should throw a unique constraint error when trying to insert a row like this one.
Another important point is that user_one_id
represents the initiator of the friendship
and user_two_id
represents the recipient, so I cannot just make the "smaller" of the two ids user_one_id
.
The question
Is there a way to do this using constraints or should I implement this some other way?