How to enforce uniqueness of compound primary key made up of two foreign keys to the same table?

Question

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?

Answer 1

In light of your comment,

For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id.

Well, you can still do it. Your use case just excludes a row-constraint to ensure that. What you want is to use a table-constraint, something like this.

CREATE TABLE friendship (
  user_one_id int NOT NULL,
  user_two_id int NOT NULL,
  CHECK (user_one_id != user_two_id ),
  PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
  greatest(user_one_id, user_two_id),
  least(user_one_id, user_two_id)
);

We have a lot going on here.. We make sure.

1. Both are NOT NULL
2. Both are not equal to each other
3. Both are UNIQUE (user_one_id, user_two_id)

That leaves one remaining problem of commutative uniqueness we solve that with a custom unique table-constraint implemented with an index.

Proof in the pudding

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

As an important friendly note your names are all kinds of silly. The relationship is fine. In production, please give them better names..

friendship
----------
request_initiator
request_target
other_fields

Answer 2

the solution is very good. But we should first ensure that there is no overlap numbers in the two columns. ie., a number will be unique to a column.

I would suggest the following simpleton solution:

create two sequences:

1.

user_one_id_seq: with even numbers

user_two_id_seq with odd numbers

2.

user_one_id_seq: numbers between say 1 to 10000

user_two_id_seq numbers between say 10001 to 20000

Answer 3

The design should be normalized by creating an intersection table named user_friendship:

create table user_friendship ( user id int NOT NULL, friendship id int NOT NULL, PRIMARY KEY (user_id, friendship_id), FOREIGN KEY friendship_id REFERENCES friendship(friendship_id), FOREIGN KEY user_id REFERENCES user(user_id)
);

Answer 4

If you set the logic to set that the value of user_one_id is lower than the value of user_two_id, you won't have any issue.