ALTER COLUMN TO REMOVE PRIMARY KEY?

Question

Post reply

ALTER COLUMN TO REMOVE PRIMARY KEY?

Tiya

Ten Centuries

Points: 1146
More actions
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

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

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

calvo SSChampion Points: 12930 More actions · Answer 1

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.

Tiya Ten Centuries Points: 1146 More actions · Answer 2

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....

calvo SSChampion Points: 12930 More actions · Answer 3

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.

Tiya Ten Centuries Points: 1146 More actions · Answer 4

Tiya

Ten Centuries

Points: 1146

May 24, 2011 at 12:45 pm

#1329326

yes it worked..thanks a lot...