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