SQL in a Nutshell, 3rd Edition

PRIMARY KEY Constraints

A PRIMARY KEY constraint declares one or more columns whose values uniquely identify each record in the table. It is considered a special case of the UNIQUE constraint. Here are some rules about primary keys:

• Only one primary key may exist on a table at a time.

• Columns in the primary key cannot have datatypes of BLOB, CLOB, NCLOB, or ARRAY.

• Primary keys may be defined at the column level for a single column key or at the table level if multiple columns make up the primary key.

• Values in the primary key column(s) must be unique and not NULL.

• In a multicolumn primary key, called a concatenated key, the combination of values in all of the key columns must be unique and not NULL.

• Foreign keys can be declared that reference the primary key of a table to establish direct relationships between tables (or possibly, though rarely, within a single table).

The following ANSI standard code includes the options for creating both a table- and column-level primary key constraint on a table called distributors. The first example shows a column-level primary-key constraint, while the second shows a table-level constraint:

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