Postgres Unique constraint based column value

Question

CREATE TABLE employee (
    id varchar(40) NOT NULL,
    legacy_id varchar(40) NOT NULL,
    short_name varchar(255) NULL,   
    is_deleted bool NOT NULL DEFAULT false,
    CONSTRAINT employee_pkey PRIMARY KEY (id)
)

I want to plan for unique constraint like this

The table must have these constraint

1. Unique constraint is required on column 'legacy_id' and 'is_deleted = false'. Its okay to have multiple values with legacy_id and is_deleted = true.

Is there any way to achieve this

Laurenz Albe · Accepted Answer · 2020-07-06 06:48:30Z

1

You cannot have a constraint for that, but a partial unique index will do just as well:

CREATE UNIQUE INDEX ON employee (legacy_id)
   WHERE is_deleted;

answered Jul 6, 2020 at 6:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres Unique constraint based column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related