SQL in a Nutshell, 3rd Edition

UNIQUE Constraints

A UNIQUE constraint, sometimes called a candidate key, declares that the values in one column, or the combination of values in more than one column, must be unique. Rules concerning unique constraints include:

• Columns in a unique key cannot have datatypes of BLOB, CLOB, NCLOB, or ARRAY.

• The column or columns in a unique key may not be identical to those in any other unique keys, or to any columns in the primary key of the table.

• A single NULL value, if the unique key allows NULL values, is allowed.

• SQL2003 allows you to substitute the column list shown in the general syntax diagram for constraints with the keyword (VALUE). UNIQUE (VALUE) indicates that all columns in the table are part of the unique key. The VALUE keyword also disallows any other unique or primary keys on the table.

In the following example, we limit the number of distributors we do business with to only one distributor per zip code. We also allow one (and only one) “catch-all” distributor with a NULL zip code. This functionality can be implemented easily using a UNIQUE constraint, either at the column or the table level:

-- Creating a column-level constraint
CREATE TABLE distributors
    (dist_id       CHAR(4)    PRIMARY KEY,
     dist_name     VARCHAR(40),
     dist_address1 VARCHAR(40),
     dist_address2 VARCHAR(40),
     city          VARCHAR(20),
     state         CHAR(2)    ,
zip           CHAR(5)    UNIQUE, phone CHAR(12) , sales_rep INT NOT NULL REFERENCES employee(empid)); -- Creating a table-level constraint CREATE TABLE distributors (dist_id CHAR(4) NOT NULL, ...