Unique indexes

A unique index ensures that you won't have more than one row containing a particular key value. Unique indexes are quite common in proper database design, both for improving performance--a unique index 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 that the primary key case is labeled better in the system catalog (and is therefore preferable to an internal database documentation standpoint). Note that the index created for the purpose of enforcing uniqueness is a perfectly functional index usable for speeding up queries. There's no need in this case to create a regular index onĀ  k so that you can look up values by the key faster, as the unique index provides that already.

It's also possible to directly create an index using CREATE UNIQUE INDEX. This is considered bad form and you should avoid doing so. It effectively creates a constraint, without labeling it as such in the list of constraints. If you want values to be unique, add a constraint; don't add the index manually.

One caveat when using unique indexes is that null values are not considered equal to one another. This means that you could have several entries inserted that each have a null value for their key. To avoid the problems this can introduce, it's recommended to always add NOT NULL to the fields that are going to participate in the index when the table is created. Rejecting null values when they don't make sense in your data is a good habit to adopt beyond this particular issue--most fields in a well-designed database will reject null values.