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 character(10)
not null unique, ...);
In the preceding example, the unique constraint ensures that no two departments have the same name.
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 depts (dname character(10) not null,
dlocation character(10) not null,
unique (dname, dlocation));
Note: 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.
Note: Unique constraints may create system indexes that cannot be explicitly dropped by the table owner. These indexes are used to enforce the unique constraint.