Transform UNIQUE constraint into CHECK or trigger

Question

I'd like to modify my table constraints. Since now, name have to be unique. But I'd like to have unique name, only when a row with given name is active. So i added column is_active.

CREATE TABLE item
(
(...)
name character varying(50) NOT NULL,
is_active boolean NOT NULL DEFAULT true,
CONSTRAINT uc_item_name UNIQUE (name),
(...)
)

Can I delete unique constraint and add trigger, or function call which would do so:

if given new name does not exists in table return true or allow insert,
if given new name exits in at least one row, iterate over them and check if at least one is active. if yes - fail insert otherwise add row to table.

Can anyone help me? It is just beginning of my adventure with Postgres. I'm using version 9.6

user330315 · Accepted Answer · 2017-02-23 17:20:16Z

4

You can create a partial unique index:

create unique index on item (name) 
where is_active;

answered Feb 23, 2017 at 17:20

user330315

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

2 Comments

Konrad Dziurdź Over a year ago

that's the anwser! thank you. I am just wondering if i'm able to do this by CHECK constraint which calls a function that returns a boolean. is it possible? I ask, because I tought it would be the most elegant way to achive my goal

Nick Barnes Over a year ago

@Konrad: Not possible at the default isolation level; two simultaneous inserts using the same name can't see each other, and both would pass the check. A unique index handles concurrency automatically.

Collectives™ on Stack Overflow

Transform UNIQUE constraint into CHECK or trigger

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related