Search code examples
postgresqltriggersconstraintsunique

Transform UNIQUE constraint into CHECK or trigger

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

Solution

  • You can create a partial unique index:

    create unique index on item (name) 
where is_active;