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.