Page 1 of 1

Adding a new column to an existing primary Key.

PostPosted: Thu Apr 13, 2017 12:59 pm
by Shanthi_Palani
Hello Experts,
Greetings.
A table is existing with a primary key made up of two columns. My new requirement is to add a new column (char(01)) to the existing primary key.
how to achieve this please?

Info on the table:
Existing Unique index - 1
Existing Primary key - 2 columns

The approach we planning to try,
1) Add the new column with the characteristics not null with default value.
2) Drop the existing Primary Key.
3) Create the unique Index on the new primary key columns.
4) Create the primary key including the new column as well.

Result:
1 and 2 are successful.
3 - no access to create to index(Sqlcode = -551)( we are requesting access)

A) Kindly let us know whether this is the approach we are to follow?
B) Also by dropping primary key , the existing unique index will get dropped on its own?

Other approach we tried and its result:
When we tried to create the primary key before creating the unique index,we were getting sqlcode = -625 (TABLE DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY OR UNIQUE KEY) .
C) This means that the existing index is dropped or there is no unique index on the three primary key columns?

Please give your inputs on the A,B,C queries when you have some time.

Thanks a ton.

Re: Adding a new column to an existing primary Key.

PostPosted: Thu Apr 20, 2017 11:27 am
by Shanthi_Palani
Hello All,
We tried the below steps and it works.

1) Add the new column with the characteristics not null with default value.
2) Drop the existing Primary Key.
3) Create the unique Index on the new primary key columns.
4) Create the primary key including the new column as well

Also by dropping the primary key, the existing unique index on that primary key is being dropped.

Thank you!