1 Comments
  •   Posted in: 
  • SQL

If you add a new table and mark the ID column as primary key, but you forgot to set the int or bigint to auto increment, you can not fix this in the SQL Management Studio.

image

There are several ways to do this, I ended up removing the primary key, dropping the column and re-creating it.

Dropping the column also gave me the error about dropping the whole table and re-creating it. So I had to do this with some SQL, so no GUI today but just these two small SQL statements. I hope it helps, otherwise it is just an online backup for when I forget how I fixed this.

alter table YOURTABLENAME add YOURCOLUMNNAME bigint IDENTITY(1,1) primary key
alter table YOURTABLENAME drop column YOURCOLUMNNAME 

Good luck!

 

ps. when you use select @@version you get to see what version of SQL server you are using. I had

Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
    Jul 11 2014 16:11:50
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
This blogpost http://sqlserverbuilds.blogspot.nl/ has a nice list of different versions.

Pin on pinterest Plus on Googleplus Post on LinkedIn

Comments

Comment by Andrew Rimmer

SQL Management Studio copes with this scenario fine if you uncheck the setting that prevents saving changes if the tables need to be re-created.

I have never had any issues with disabling this setting.