UNIQUE on 2 columns allows duplicates in Postgres-CodePudding

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?

CodePudding user response：

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.