Adding existing column as primary key in DB2 with existing primary keys

Question

I have a table MY_TABLE in DB2 containing several columns:

PRODUCT_ID
ADDED_DT
PRODUCT_NAME
PRODUCT_COLOR
PRODUCT_PRICE
EXPIRY_DT

Out of these columns, below columns form the primary key:

PRODUCT_ID
ADDED_DT

I want to add EXPIRY_DT as primary key such that the table will now have three primary keys viz. PRODUCT_ID, ADDED_DT and EXPIRY_DT

I tried below set of commands:

ALTER TABLE MY_TABLE ADD PRIMARY KEY (EXPIRY_DT);
REORG TABLE MY_TABLE;

However, the query failed with error:

Table "MY_TABLE" already has a "PRIMARY" key.

How to acheive this ?

Answer 1

First you need to drop the existing primary key and then add new primary key

ALTER TABLE Table_Name DROP PRIMARY KEY;

ALTER TABLE Table_Name ADD PRIMARY KEY (Column_One, Column_Two);