What is the definition and usage of alternate key in a DB2 table?

The DB2 table contains a number of columns whose value will remain unique in the entire table. Among these multiple columns only one column is selected as the primary key and the remaining keys are known as candidate keys.

We can declare any candidate key as an alternate key. Which means that the value of this key cannot take duplicate value, however unlike primary key the primary index is not built on the alternate key.

We can define alternate key while defining any table using a UNIQUE keyword. For example, if we want to make TRANSACTION_ID as an alternate key then−

CREATE TABLE ORDERS
   (ORDER_ID CHAR(15) NOT NULL,
   ORDER_DATE DATE,
   ORDER_TOTAL DECIMAL(9,2),
   TRANSACTION_ID CHAR(15),
   PRIMARY KEY(ORDER_ID),
   UNIQUE(TRANSACTION_ID))
IN DB4ES01;

Mandalika

Published on 14-Sep-2020 17:21:14