0

I have a friends table

| id | requestor | requestee | status |

requestor and requestee are both userIds

I want to have a unique set of relationships so if id:1 requests to be friends with id:2 the visa versa would not be allowed.

I tried adding a unique constraint with the requestor and requestee columns, but that allowed 1 2 if 2 1 existed.

How else can I do this? is there some sort of flag when making a constraint to make it cover both combinations?

I am also open to structuring it differently/ adding new tables if it is the right way to accomplish this.

Many thanks

William Daniels
  • 67
  • 5

1 Answers1

4

You can create a unique index that always indexes the same order of values:

create unique index 
   on friends (least(requestor, requestee), greatest(requestor, requestee));