ADD Primary key to existent table

[Former Member]

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.

[pfefferf]

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.