Post reply

ALTER COLUMN TO REMOVE PRIMARY KEY?

  • May 24, 2011 at 12:05 pm

    #250706

    HI ,

    i want to alter a column and want to remove the primary key but getting error message

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'IDENTITY'.

    my code is

    CREATE TABLE T (

    int1 int IDENTITY PRIMARY KEY,

    bit1 bit NOT NULL DEFAULT 0)

    ALTER TABLE T

    ALTER COLUMN INT1 INT IDENTITY

  • May 24, 2011 at 12:09 pm

    #1329292

    ALTER TABLE [tableName]

    DROP CONSTRAINT [constraintName]

    will remove the constraint, then you can modify the column attributes.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • May 24, 2011 at 12:17 pm

    #1329300

    If i drop the whole constraint i will loose identity also..is there any way that will drop primary key but will keep identity constraint....

  • May 24, 2011 at 12:27 pm

    #1329311

    create table test(col1 int identity primary key not null, col2 int)

    --check table design. identity specification = yes

    insert into test (col2)

    values (2),(5),(23)

    alter table dbo.test

    drop constraint [constraintName]

    --check table design. identity specification = yes

    have you tested it on a test table?

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • May 24, 2011 at 12:45 pm

    #1329326

    yes it worked..thanks a lot...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply