UNIQUE

The UNIQUE constraint prevents duplicate values from appearing in the column. It is implemented by creating a unique index on a column. As indicated in Figure , UNIQUE prevents duplicates.  

        test=> CREATE TABLE uniquetest (col1 INTEGER UNIQUE);
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest_col1_key' for table 'uniquetest'
        CREATE
        test=> \d uniquetest
               Table "uniquetest"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
        Index: uniquetest_col1_key 
         
        test=> INSERT INTO uniquetest VALUES (1);
        INSERT 148620 1
        test=> INSERT INTO uniquetest VALUES (1);
        ERROR:  Cannot insert a duplicate key into unique index uniquetest_col1_key
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT 148622 1
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT  

CREATE TABLE displays the name of the unique index created. The figure also shows that multiple NULL values can be inserted into a UNIQUE column.

If a UNIQUE constraint consists of more than one column, UNIQUE cannot be used as a column constraint. Instead, you must use a separate UNIQUE line to specify the columns that make up the constraint. This approach creates a UNIQUE table constraint.

Figure  shows a multicolumn UNIQUE constraint.  

        test=> CREATE TABLE uniquetest2 (
        test(>                           col1 INTEGER, 
        test(>                           col2 INTEGER, 
        test(>                           UNIQUE (col1, col2)
        test(>                          );
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest2_col1_key' for table 'uniquetest2'  

While col1 or col2 themselves may not be unique, the constraint requires the combination of col1 and col2 to be unique. For example, in a table that contains the driver's license numbers of people in various states, two people in different states might have the same license number, but the combination of their state and license number should always be unique.