Unique indexes

A unique indexes enforces that you won't have more than one row containing a particular key value. These are quite common in proper database design, both for improving performance—an indexed unique lookup is normally fast—as well as data integrity, preventing erroneous duplications of data. Only B-tree indexes can currently be used as unique ones.

There are three ways you can create a unique index, only two of which are recommended. The first you saw at the beginning of the chapter walkthrough: when you mark a field as PRIMARY KEY, a unique index is created to make sure there are no key duplicates.

But a primary key is just a specially marked case of having a unique constraint on a table. The following two statement sets give almost identical results:

CREATE TABLE t(k serial PRIMARY KEY,v integer);

CREATE TABLE t(k serial,v integer);
ALTER TABLE t ADD CONSTRAINT k_key UNIQUE (k);

Except, the primary key case is labeled better in the system catalog...