CODEWITHSUNDEEP
sqlsql-server-2008alter
Ram
Ram

Reputation: 11

SQL Server 2008: ALTER TABLE query

I am getting error for the below code

ALTER TABLE ADM_Roles ALTER COLUMN RoleID int IDENTITY (1, 1)

Incorrect syntax near the keyword IDENTITY.

Upvotes: 1

Views: 20376

Answers (3)

marc_s
marc_s

Reputation: 754368

You cannot change an existing column into an IDENTITY column - you will need to add a new column that has the identity flag:

ALTER TABLE dbo.ADM_Roles 
 ADD NewRoleID INT IDENTITY (1, 1)

If you need to, you can later on drop the old column and rename the new column to the old name:

ALTER TABLE dbo.ADM_Roles DROP COLUMN RoleID

EXEC sp_rename @objName = 'dbo.ADM_Roles.NewRoleID', 
               @newName = 'RoleID', 
               @objType = 'COLUMN'

Upvotes: 6

MiBu
MiBu

Reputation: 869

From the MSDN

You can't alter the existing columns for identity.

You have 2 options,

  1. Create a new table with identity & drop the existing table

  2. Create a new column with identity & drop the existing column But take special care when these columns have any constraints / relations.

Example approach:

  • In this approach you can’t retain the existing data values on the newly created identity column;

  • The identity column will hold the sequence of number

    Alter Table Names Add Id_new Int Identity(1,1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID','Column'

Upvotes: 0

JAiro
JAiro

Reputation: 5999

you have to remove the word "int".

ALTER TABLE ADM_Roles ALTER COLUMN RoleId IDENTITY (1, 1);

Upvotes: -1

Related Questions