Do unique constraints get checked one by one?-CodePudding

We have a simple users table

CREATE TABLE users (id uuid, name varchar)

users can propose each other, so we have another table called proposals

CREATE TABLE user_proposals (id uuid, user_id uuid, proposed_user_id uuid)

We want to define a unique constraints so a user can't propose another specific user twice.

Does defining unique constraints with columns of (user_id, proposed_user_id) also prevents proposed_user_id from proposing user_id ? in other words, does postgres check unique constraint in reverse as well? (proposed_user_id, user_id)

CodePudding user response：

If you really need to assert that no user pairing exists in either direction, you would have to use an insert trigger. A constraint can only assert something on the values being inserted in that record, not against the entire table.

One suggestion here might be to create a unique constraint on (user_id, proposed_user_id), and also add a constraint asserting that user_id is always less than proposed_user_id:

CREATE TABLE user_proposals (
    id uuid,
    user_id uuid,
    proposed_user_id uuid,
    UNIQUE(user_id, proposed_user_id),
    CHECK (user_id < proposed_user_id)
)

With this definition in place, you only would need to ensure that you insert the smaller id value for user_id, and the larger for proposed_user_id. The constraints would handle the rest.

CodePudding user response：

I suggest a unique index:

CREATE UNIQUE INDEX ON users (
   least(user_id, proposed_user_id),
   greatest(user_id, proposed_user_id),
);