UNIQUE on 2 columns allows duplicates in Postgres

Question

I've this many-to-many table that connects the person table to itself:

CREATE TABLE IF NOT EXISTS friendship(
    person_id INTEGER NOT NULL,
    friend_id INTEGER NOT NULL,
    date_friendship DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY(person_id, friend_id),
    FOREIGN KEY(person_id) REFERENCES person(id),
    FOREIGN KEY(friend_id) REFERENCES person(id),
    UNIQUE(friend_id, person_id)
);

INSERT INTO friendship (person_id, friend_id)
VALUES (1, 2),
        (1, 3),
        (2, 3),
        (4, 2);

inserting a row like (person_id, friend_id) (2, 1) should be prohibted. Because we already inserted (1, 2). I mean if 1 is friend of 2 then also 2 is friend of 1.

I can't enforce such constraint. I tried UNIQUE(friend_id, person_id) but I'm still able to insert:

INSERT INTO friendship (person_id, friend_id)
VALUES (2, 1);

How should I handle such case? Should I use a different kind of database?

check pid < fid? (I.e. always store the one with lowest id as person, and the other one as friend.) However, why not consider always storing both combinations? — jarlh
– jarlh, Commented Nov 16, 2022 at 12:27
Good idea but inserting the 4th row "(4, 2);" wouldn't work then. My goal is to not store redundant data. — Muhammad Ihab
– Muhammad Ihab, Commented Nov 16, 2022 at 12:38

user330315 · Accepted Answer · 2022-11-16 12:30:01Z

1

You can create a unique index on the "normalized" combination of the two values:

create unique index  
   on friendship (least(person_id,friend_id), greatest(person_id, friend_id) );

You can remove the unique(friend_id, person_id) from your table definition then.

answered Nov 16, 2022 at 12:30

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammad Ihab Over a year ago

That's brilliant! I should've thought of this. Thank you.

Collectives™ on Stack Overflow

UNIQUE on 2 columns allows duplicates in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related