Key Declaration

key_def:
        key_spec (column_name [ASC | DESC] [, column_name [ASC | DESC]...])

The key can be declared to include one or more columns from the table. Each column in the key definition can be declared to occur in ascending (ASC) or descending (DESC) order. The default order is ascending (ASC).

Examples

create table salesperson
(
   sale_id char(3) primary key,
   sale_name char(30) not null,
   dob date,
   commission decimal(4,3),
   region smallint,
   sales_tot double,
   office char(3) references outlet(loc_id),
   mgr_id char(3) references salesperson,
   constraint sales_regions key (region, office),
   constraint sales_comms key (commission desc, sale_name)
);

In the example above, the "sales_regions" key is defined as a combination of the "region" and "office" columns. Both of these columns would be declared to be in ascending order. The next line declares the "sales_comms" key as a combination of the "commission" and "sale_name" columns. In this key, the commission column will be sorted in descending order and for each commission rate, the rows will be sorted by the sale_name column in ascending order.