Transform UNIQUE constraint into CHECK or trigger

0
votes

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

1
postgresqltriggersconstraintsunique

1 Answers

4
votes

You can create a partial unique index:

create unique index on item (name) 
where is_active;