This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Similar to keys, indices can be specified as unique. This means that the specified column does not contain rows with a repeating value. Multi-columns can also be made unique. When using unique multi-columns, the combined values of the columns in the index cannot have a repeating combined value.
![]() | Note: NULL |
---|---|
NULLs are a special case. An index with several NULL values still constitute it unique, even though there are repeated NULL values in the table. This is because a NULL is not equal to another NULL. Since NULL is unknown, there is no way to compare an unknown value with another unknown value and conclude that both values are equal to each other. |
To create a unique index, use the following command:
CREATE UNIQUE INDEX name ON table (column [, ...]);
Unique indices only works for the B-tree index type. Once a table is declared with the optional values: unique or primary key, a unique index is automatically created by PostgreSQL. If a table was created without a primary key or the unique option, then a unique index could be added at a later time. But a primary key cannot be added after the table has been created.
 
Continue to: