Monday, March 3, 2014

Prevent saving changes that require the table re-creation (SQL Server Error in Updating Primary Key Is Identity)

Good Day!

I forgot to set the identity of the primary key of field Id in table Products. After inserting records through insert statements, an error occurs since the column Id does not allow null values. So, I tried changing the Is Identity to true but unluckily I'm stuck with the an error "Prevent saving changes that require the table re-creation". Additional details are as follows: ID field has a constraint called PK_Products. So, after googling a while I came up with series of steps.

Note: You have to close the design view of the table in the Management Studio to perform the steps below.
 -- 1  
 ALTER TABLE Products DROP CONSTRAINT PK_Products  
 GO  
 -- 2  
 Alter Table Products Drop Column Id  
 Go   
 -- 3  
 Alter Table Products Add Id_new Int Identity(1,1)  
 Go   
 -- 4  
 Exec sp_rename 'Products.Id_new', 'Id','Column'  
 Go  
 -- 5  
 ALTER TABLE Products Add CONSTRAINT PK_Products Primary Key (ID)  
 GO  
Where: Products is the table name and Id as the primary key field. Additional information and explanations are found in the links provided below:

References:
a. Alter Constraint
b. How to alter column to identity(1,1)
Cheers!

0 comments:

Post a Comment