none
adding an identity column to existing table

    Question

  • I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.

    thx

    Kat

    Monday, September 11, 2006 11:53 PM

Answers

  • Just add PRIMARY KEY:

    create table test
    (
        value varchar(10)
    )
    go
    insert into test
    select 'a'
    union all
    select 'b'
    union all
    select 'c'
    go
    select *
    from   test
    /*
    value
    ----------
    a
    b
    c
    */

    alter table test
        add testId int constraint PKTest identity primary key
    go
    select *
    from test

    The constraintPKTest in bold italics is optional

    Tuesday, September 12, 2006 8:40 PM
  • Even though we allow the syntax below:
     
    alter table test
        add testId int constraint PKTest identity primary key
     
    I think it is a bug to allow the IDENTITY keyword in between the constraint definition. The correct syntax is:
     
    alter table test
        add testId int identity constraint PKTest primary key
     
     
    I will file a bug internally for the invalid syntax.
    Tuesday, September 12, 2006 11:27 PM

All replies

  • The alter table didn't work.  Just created a new table with the identity column and inserted into that.  So never mind...  Unless there is a different way to do this.  I know, it has been a while for me.

    Kat

    Tuesday, September 12, 2006 1:54 AM
  • It should:

    create table test
    (
        value varchar(10)
    )
    go
    insert into test
    select 'a'
    union all
    select 'b'
    union all
    select 'c'
    go
    select *
    from   test
    /*
    value
    ----------
    a
    b
    c
    */

    alter table test
        add testId int identity
    go
    select *
    from test

    Can you post the syntax/error you got if it doesn't work for you?

    Tuesday, September 12, 2006 3:07 AM
  • Hi Louis,

    This did work.  I think my syntax was the problem when altering the table, which I discarded.  I was trying to add a primary key constraint at the same time and my syntax was obviously messed up.  I followed the BOL syntax for altering a table, adding an identity column along with a primary key constraint.  It didn't appear to say that this was not possible unless I read the 'alter table' section incorrectly.  Can you tell me if this is possible and what the correct 'alter table' add identitiy, and make it a primary key at the same time?

    thx,

    Kathleen

     

    Tuesday, September 12, 2006 1:54 PM
  • Just add PRIMARY KEY:

    create table test
    (
        value varchar(10)
    )
    go
    insert into test
    select 'a'
    union all
    select 'b'
    union all
    select 'c'
    go
    select *
    from   test
    /*
    value
    ----------
    a
    b
    c
    */

    alter table test
        add testId int constraint PKTest identity primary key
    go
    select *
    from test

    The constraintPKTest in bold italics is optional

    Tuesday, September 12, 2006 8:40 PM
  • Even though we allow the syntax below:
     
    alter table test
        add testId int constraint PKTest identity primary key
     
    I think it is a bug to allow the IDENTITY keyword in between the constraint definition. The correct syntax is:
     
    alter table test
        add testId int identity constraint PKTest primary key
     
     
    I will file a bug internally for the invalid syntax.
    Tuesday, September 12, 2006 11:27 PM
  • That was a mistake.  I can't believe I didn't notice that.  I also can't believe it compiled :)
    Wednesday, September 13, 2006 1:39 AM