Unique Constraint
To ensure that no two rows have the same value in a particular column or set of columns, specify UNIQUE NOT NULL.
Note: If a column is specified as UNIQUE, NOT NULL must also be specified.
The following example of a column-level unique constraint ensures that no two departments have the same name:
CREATE TABLE dept(dname CHAR(10) UNIQUE NOT NULL, ...);
To ensure that the data in a group of columns is unique, specify the unique constraint at the table level (rather than for individual columns). A maximum of 32 columns can be specified in a table-level unique constraint.
The following example of a table-level unique constraint ensures that no two departments in the same location have the same name. The columns are declared not null, as required by the unique constraint:
CREATE TABLE project (
proj_id INT NOT NULL NOT DEFAULT,
proj_dept_id INT NOT NULL WITH DEFAULT,
proj_name CHAR(25) NOT NULL,
UNIQUE (proj_id) WITH STRUCTURE = HASH);
Any column or set of columns that is designated as the primary key is implicitly unique and must be specified as NOT NULL. A table can have only one primary key, but can have any number of unique constraints. For example:
CREATE TABLE project (
proj_id INT NOT NULL NOT DEFAULT,
proj_dept_id INT NOT NULL WITH DEFAULT,
proj_name CHAR(25) NOT NULL UNIQUE,
UNIQUE (proj_dept_id) WITH STRUCTURE = HASH,
PRIMARY KEY (proj_id));
Note: Unique constraints create system indexes that cannot be explicitly dropped by the table owner. The indexes are used to enforce the unique constraint.