0

I have an existing table MCheckTypes and it has a primary key column ID. What I want to do is to alter the table to add identity type on the primary key. I don't want to drop a table and recreate a new one. I already google it but no luck. Below is my script

ALTER TABLE MCheckTypes 
   ALTER COLUMN [ID] INT IDENTITY(1, 1) NOT NULL

below is the error message

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bryan
  • 1,245
  • 5
  • 22
  • 37

1 Answers1

1

You cannot "convert" an existing column into an IDENTITY column - you have to create a new column as INT IDENTITY:

ALTER TABLE MCheckTypes 
ADD NewID INT IDENTITY (1, 1) NOT NULL;
Dhwani
  • 7,484
  • 17
  • 78
  • 139
  • So annoying, so I need to drop existing column and recreate a new one? Why SQL have this kind of restriction? – Bryan Nov 21 '14 at 07:04
  • @Bryan No. you can keep current one. once you create newid column you can drop old column and rename the newID as ID. but all your references will be lost which is based on old id. – Dhwani Nov 21 '14 at 07:04
  • But I want the same field name so I don't need to adjust my application code. Is it possible to have the same field name on 1 table? – Bryan Nov 21 '14 at 07:06
  • @Bryan no. you can't have two column with same name. – Dhwani Nov 21 '14 at 07:07
  • yes that's it. create new column, drop old, rename the new column. – Bryan Nov 21 '14 at 07:07
  • @Bryan but beware, If your current ID has reference in another tables. You can't drop column or your references will be mismatched. – Dhwani Nov 21 '14 at 07:08
  • yeah,thank you. but I can't see why MSSQL create this kind of restriction that doesn't allow existing column to be an IDENTITY type – Bryan Nov 21 '14 at 07:12
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/65338/discussion-between-dh-and-bryan). – Dhwani Nov 21 '14 at 07:12