CODEWITHSUNDEEP
sqlpostgresqlunique-constraint
Dainius
Dainius

Reputation: 1852

Unique constrain for values combination in Postgresql

is it possible to create unique constrain in Postgresql, that would take into account column values and work with some value, but not with another?

I have table, where items have "code" that must be unique, but records aren't deleted from table, just marked as deleted (special boolean field "deleted"). I could create unique(code, deleted) to enforce uniqueness, it works if I don't mark 2 rows as deleted with same "code". If I would try to "delete" second row, it will alert that 2 rows will have same unique values and doesn't allow. What I would like to have, is check unique field "code" for every row, that "deleted" is false.

Upvotes: 1

Views: 632

Answers (1)

GMB
GMB

Reputation: 222482

You can't do this with a consraint. However, in Postgres, a partial unique index would do exactly what you want:

create unique index myindex on mytable(code) where (deleted = false);

You can also phrase this as:

create unique index myindex on mytable(code) where (not deleted);

Upvotes: 2

Related Questions