For some applications, it is convenient to clearly identify the rows of a table by one or more columns to be able to process the table, for example, in a fixed sequential order. To do so, a primary key is defined. The column names that are to form the key of the table are preceded by 'PRIMARY KEY'. The input values of the key columns thus defined must not be NULL.
The rows of the table 'city' are to be uniquely identified by the zip code. A single-column primary key is created for this purpose. Within the table definition, the zip code need not necessarily be defined as the first column.
CREATE TABLE city (name CHAR(15),
state CHAR(2) NOT NULL,
zip FIXED(5),
PRIMARY KEY (zip))
There is a syntactic variant for defining a table with a key. The key columns are provided with the attribute KEY. In the CREATE TABLE statement, they must be listed as the first columns in the order of definition.
CREATE TABLE city (zip FIXED(5) KEY,
name CHAR(15),
state CHAR(2) NOT NULL)
Rows are inserted in the same way as into a base table without a KEY definition. The following examples refer to the above table definition:
INSERT city
VALUES (90013,'Los Angeles','CA')
INSERT city
VALUES (75243,'Dallas','TX')
INSERT city
VALUES (90018,'Los Angeles','CA')
INSERT city
VALUES (10580,'New York','NY')
INSERT city
VALUES (20037,'Washington','DC')
INSERT city
VALUES (90029,'Hollywood','CA')
Another attempt to insert a city having the zip code 90013 results in an error message. The KEY definition ensures the uniqueness of the column.