How to Ensure There is One Row... part 2

Author: Szymon Lipiński
Published at: 2011-11-09

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';

The Index Description