Effects of Unique Constraints on Sort Order Options

When a column or list of columns is defined as unique in a CREATE TABLE or ALTER TABLE statement, the database server implements that UNIQUE CONSTRAINT by creating a unique ascending index. Thus, you cannot use the CREATE INDEX statement to add an ascending index to a column or column list that is already defined as unique.

However, you can create a descending index on such columns, and you can include such columns in composite ascending indexes in different combinations. For example, the following sequence of statements is valid: 
CREATE TABLE customer (
   customer_num  SERIAL(101) UNIQUE,
   fname                CHAR(15),
   lname                CHAR(15),
   company              CHAR(20),
   address1             CHAR(20),
   address2             CHAR(20),
   city                 CHAR(15),
   state                CHAR(2),
   zipcode              CHAR(5),
   phone                CHAR(18)
   );

CREATE INDEX c_temp1 ON customer (customer_num DESC);
CREATE INDEX c_temp2 ON customer (customer_num, zipcode);

In this example, the customer_num column has a unique constraint placed on it. The first CREATE INDEX statement places an index sorted in descending order on the customer_num column. The second CREATE INDEX includes the customer_num column as part of a composite index. For more information on composite indexes, see Creating Composite Indexes.