Unique Indexes

Unique indexes resemble ordinary indexes, except that they prevent duplicate values from occurring in the table. Figure  shows the creation of one table and a unique index.  

        test=> CREATE TABLE duptest (channel INTEGER);
        CREATE
        test=> CREATE UNIQUE INDEX duptest_channel_idx ON duptest (channel);
        CREATE
        test=> INSERT INTO duptest VALUES (1);
        INSERT 130220 1
        test=> INSERT INTO duptest VALUES (1);
        ERROR:  Cannot insert a duplicate key into unique index duptest_channel_idx  

The index is unique because of the keyword UNIQUE. The remaining queries try to insert a duplicate value, but the unique index prevents this and displays an appropriate error message.

Sometimes unique indexes are created only to prevent duplicate values, not for performance reasons. Multicolumn unique indexes ensure that the combination of indexed columns remains unique. Unique indexes do allow multiple NULL  values, however. Unique indexes both speed data access and prevent duplicates.