Table constraint for data pair regardless of order

Question

I have two tables; friends and users.

The Users table, slightly simplified:

CREATE TABLE users (
    id bigserial NOT NULL,
    fname text,
    lname text,
    username text,
    created timestamp without time zone DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id)
)

And the Friends table is a join table

CREATE TABLE friends
(
    user_id bigint,
    friend_id bigint,
    accepted boolean DEFAULT false
)

So the friends table just maps one user to another, so user 1 can be friends with user 2.

My question: How can I constrain the table so that two pairs (1, 2) , (2,1) do not get inserted into the table?

I know I can do unique (user_id , friend_id) to prevent duplicate rows like (1,2) from being inserted...

But how do I prevent (2, 1) from being inserted if (1, 2) already exists?

Possible duplicate from a few days ago: http://stackoverflow.com/q/33705305/939860 — Erwin Brandstetter, Nov 21 '15 at 01:04

Gordon Linoff · Accepted Answer · 2015-11-20T20:36:43.310

Use an index on an expression:

create unique index unq_friends_user_friend
    on (least(user_id, friend_id), greatest(user_id, friend_id));

EDIT:

I need to elaborate, because of the other answer.

Notice that this approach involves one line of code in the database (okay, I admit I split it into two in the answer ;)

Attempting to replicate this code at the application layer requires:

A complex query to check for the existence of either pair.
I should note that this complex query is hard to optimize (particularly as formulated in the answer).
Application code to run the query and check the results before any insert.
Oh, and the same thing for updates as well. Hmmm, it is left to the reader as an exercise how to handle updates, particularly multiple row updates.

After going through all this trouble, you still have problems with race conditions. Two threads could attempt to insert the same two friends. Neither sees a conflict. Both do the insert. Now, you have duplicates.

This makes the one-line solution really a no-brainer.

Bad plan: use constraints, not indexes, to enforce constrants. IMHO. — wildplasser, Nov 21 '15 at 00:25
Unique constraints and unique indexes do essentially the same thing (unique indexes are used for implementing unique constraints). The one small advantage of unique constraints is that the name of the constraint is used by most databases when reporting an error. — Gordon Linoff, Nov 21 '15 at 03:53

Table constraint for data pair regardless of order

1 Answers1