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

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?

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.

• Both are NOT NULL

• Both are not equal to each other

• 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

StackExchange Database Administrators Q#163681, answer score: 8