Skip to Content
avatar image
Former Member

ADD Primary key to existent table

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 31, 2017 at 11:28 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded