Candidate keys

If you design a table to require that a column or combination of columns define a row as unique, you define the columns with a candidate key constraint.

In the following example, the employee number (empno) is the primary key in the employee table because it uniquely identifies each row. Each entry in the employee social security column must also be distinct. Because a primary key has been designated already for the table, you must place a candidate key constraint on the ss_no column.

 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
     ) ;

You declare a column as a candidate key by using the keyword UNIQUE. Precede the UNIQUE keyword with the NOT NULL specification. Like a primary key, a candidate key also uniquely identifies a row in a table. Note that a table can have only one primary key, but can have any number of candidate keys.

If you supply a duplicate value for a candidate key in an INSERT or UPDATE operation, the operation returns an error.