Candidate Keys

A column or a set of columns from a table are to be declared as a candidate key, if it is to be enforced that the values for the column or the set of columns be distinct for each row of the table. For example, in an employee table,the employee number uniquely identifies the row, and is usually the primary key of the employee table. But there exists another column containing the social security number for the employee. Since it is to be enforced that the values for this column be distinct for each row in the table, this column can be declared as a candidate key.

The following example shows the specification of a candidate key on the employee table:

CREATE TABLE employee (

     empno        INTEGER NOT NULL PRIMARY KEY,

     ss_no        INTEGER NOT NULL UNIQUE,

     ename        CHAR (19),

     sal          NUMERIC (10, 2),

     deptno       INTEGER NOT NULL

) ;

A column is declared as a candidate key using the keyword UNIQUE. It can be noted that the UNIQUE specification has to be preceded by a NOT NULL specification for that column.

Like a primary key, a candidate key also uniquely identifies a row in a table. But, whereas a table can have at most one primary key, there could be any number of candidate keys on a table.

During the INSERT/UPDATE operation, if a duplicate value is given for a candidate key, the INSERT/UPDATE operation results in an error.