How to Ensure There is One Row… part 2

Some time ago I wrote about ensuring that there is only one row with a specific value in a column.

Today a very similar problem: there is a table like this:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  value TEXT NOT NULL
);

We need to be sure that there is only one row withing specific name with value=’a’. There can be more rows with this name, but only one can have value ‘a’.

For achieving that requirement I’ve created an index:

CREATE UNIQUE INDEX i_test 
ON test (name, value) 
WHERE value = 'a';

Index Description

  • This index is made only on the rows where value = ‘a’.
  • For all those rows (and remember: there should be only one such row) this index contains only the pair of columns (name, value).
  • This index is UNIQUE so there can only be unique values (in this case: only one row with the value true).
  • Pair of columns is unique only if both arguments are unique.

2 responses

Leave a Reply

Your email address will not be published. Required fields are marked *