Adding a new column to an existing primary Key.



IBM's flagship relational database management system

Adding a new column to an existing primary Key.

Postby Shanthi_Palani » Thu Apr 13, 2017 12:59 pm

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.
Shanthi_Palani
 
Posts: 10
Joined: Mon Oct 10, 2016 3:08 pm
Has thanked: 6 times
Been thanked: 0 time

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

Postby Shanthi_Palani » Thu Apr 20, 2017 11:27 am

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!
Shanthi_Palani
 
Posts: 10
Joined: Mon Oct 10, 2016 3:08 pm
Has thanked: 6 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post