CODEWITHSUNDEEP
sql-serverauto-incrementidentity
Syed Hassan
Syed Hassan

Reputation: 438

Error occurred while changing is Identity to no in SQL Server

I have to change the auto increment on ID to explicitly define ID. For this I Go to datatabse-> tables -> mytable -> design. There I set is dentity (under identity specification) to No. But when I click save it throws an error saying.

Saving changes is not permitted. The changes you have made require the following tables to 
be droped and re created....

Is there any way to do it without dropping the table. I searched this error and got the solution to run a following query

SET IDENTITY_INSERT mytable ON GO

But when I try to insert from code, it throws error that 

 Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is set to OFF

Is there any way to get out of this problem

Upvotes: 0

Views: 200

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

Once identity, always identity. You cannot change the identity property on a column. Technically, you could use IDENTITY_INSERT to get around it, but this requires setting the option on every single insert you do (this setting doesn't persist over sessions). This is probably not what you want.

Your only alternative, if recreating the table isn't an option, is to create a new column that isn't an identity column, then dropping the old one:

ALTER TABLE MyTable ADD NotAnID INT NULL;
GO
BEGIN TRANSACTION
    UPDATE MyTable SET NotAnID = ID;
    ALTER TABLE MyTable ALTER COLUMN NotAnID INT NOT NULL;
    ALTER TABLE MyTable DROP COLUMN ID;
    EXECUTE sp_rename 'MyTable.NotAnID', 'ID';
COMMIT;

This assumes your identity column is NOT NULL (as it usually is), that ID is not the primary key, that it isn't participating in foreign key constraints, and that you want the new column to take place of the old one.

If ID is the primary key, this exercise gets more involved because you need to drop the primary key constraint and recreate it -- which has its own challenges. Doubly so if it's also the clustered index. In this case, you are probably better off recreating the table anyway, because recreating the clustered index means the whole table is rewritten -- this will almost certainly interrupt production work, so you may as well let SSMS do the tough work for you. To allow that, go to Tools -> Options -> Designers and uncheck "Prevent saving changes that require table re-creation".

Upvotes: 1

Related Questions