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.
