Skip to Main Content
SQL in a Nutshell, 3rd Edition
book

SQL in a Nutshell, 3rd Edition

by Kevin Kline
November 2008
Intermediate to advanced content levelIntermediate to advanced
591 pages
17h 28m
English
O'Reilly Media, Inc.
Content preview from 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, ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

John L. Viescas, Douglas J. Steele, Ben G. Clothier
SQL in a Nutshell, 4th Edition

SQL in a Nutshell, 4th Edition

Kevin Kline, Regina O. Obe, Leo S. Hsu

Publisher Resources

ISBN: 9780596155322Errata Page