PRIMARY KEY Constraints
A PRIMARY KEY constraint declares one or more columns whose value(s) 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 data types 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),
dist_address1 VARCHAR(40),
dist_address2 VARCHAR(40),
city VARCHAR(20),
state CHAR(2) ,
zip CHAR(5) ,
phone CHAR(12) ,
sales_rep INT ,CONSTRAINT pk_dist_id PRIMARY KEY (dist_id));
In the example showing a table-level primary key, we could easily have created a concatenated key by listing several columns separated by commas.