Skip to Content
0

ADD Primary key to existent table

May 31, 2017 at 08:55 AM

41

avatar image

Hello,

I have a table with around 1000000 entries but I don't have any primary key for this table.

How I can create an incremental column and at the same time it should be a primary key for the whole table.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
May 31, 2017 at 11:28 AM
0

Hello Nahel,

following example coding shows you how you can add an additional identity column to your table which is set as primary key. By the usage of an identity column the values for the new column do not have to be set manually by you.

-- create a dummy demo table
create column table "MISC"."TEST_ADD_PRIMKEY"(
    col1 integer,
    col2 integer,
    col3 integer
);

-- insert dummy entries into dummy table
insert into "MISC"."TEST_ADD_PRIMKEY" values (1,1,1);
insert into "MISC"."TEST_ADD_PRIMKEY" values (2,2,2);
insert into "MISC"."TEST_ADD_PRIMKEY" values (3,3,3);

-- add identity column
alter table "MISC"."TEST_ADD_PRIMKEY" add (id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1));

-- set identity column as primary key
alter table "MISC"."TEST_ADD_PRIMKEY" add primary key (id);

-- check content of dummy table => it can be seen that the primary key 
-- column is already filled with unique values due to the idendity column
select * from "MISC"."TEST_ADD_PRIMKEY";

Regards,
Florian

PS: All that stuff can be found in the official help.

Share
10 |10000 characters needed characters left characters exceeded