Using the UNIQUE or DISTINCT Constraints

Use the UNIQUE or DISTINCT keyword to require that a column or set of columns accepts only unique data values. You cannot insert values that duplicate the values of some other row into a column that has a unique constraint. When you create a UNIQUE or DISTINCT constraint, the database server automatically creates an internal index on the constrained column or columns. (In this context, the keyword DISTINCT is a synonym for UNIQUE.)

You cannot place a unique constraint on a column that already has a primary-key constraint. You cannot place a unique constraint on a BYTE or TEXT column.

As previously noted, you cannot place a unique or primary-key constraint on a BLOB or CLOB column of Dynamic Server.

Opaque data types support a unique constraint only where a secondary-access method supports uniqueness for that type. The default secondary-access method is a generic B-tree, which supports the equal( ) operator function. Therefore, if the definition of the opaque type includes the equal( ) function, a column of that opaque type can have a unique constraint.

The following example creates a simple table that has a unique constraint on one of its columns:

CREATE TABLE accounts
   (acc_name  CHAR(12), 
    acc_num   SERIAL UNIQUE CONSTRAINT acc_num) 

For an explanation of the constraint name, refer to Declaring a Constraint Name.