CODEWITHSUNDEEP
postgresql
Denis Matafonov
Denis Matafonov

Reputation: 2802

How to create constraint, based on field value

I have a table with tariffs.

Table has three columns: id INT, reception INT, is_default INT (0/1);

I need to make constraint, which will restrict creation of new tariff for the same reception with is_default = 1. So each reception could have only one default tariff.

Upvotes: 0

Views: 105

Answers (1)

pozs
pozs

Reputation: 36214

You'll need a unique partial index for that:

CREATE UNIQUE INDEX uniq_tariff_reception_where_is_default
  ON tariff (reception)
  WHERE is_default = 1

UNIQUE indexes work exactly the same as UNIQUE constraints (under the hood, UNIQUE constraints actually creates UNIQUE indexes to achieve uniqueness), but the constraint version cannot be partial:

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

Notes: please consider using the BOOLEAN type for the is_default column. With that, your index could be WHERE is_default = TRUE or even WHERE is_default.

Upvotes: 1

Related Questions