Search code examples
sqlsql-serversql-server-cesql-server-ce-4

IDENTITY_INSERT and SQL Server Compact

The script below creates a table, temporarily turns off auto identity, inserts records whilst specifying the PK value, then re-enables auto identity.

DROP TABLE Foo;
GO

CREATE TABLE Foo (Id int IDENTITY (1,1) NOT NULL, 
                  Bar nvarchar(100) NOT NULL);
GO

ALTER TABLE Foo ADD CONSTRAINT FooConstraint PRIMARY KEY (Id);
GO

SET IDENTITY_INSERT Foo ON;
GO

INSERT INTO Foo(Id, Bar) VALUES (1, 'a');
GO
INSERT INTO Foo(Id, Bar) VALUES (2, 'b');
GO
INSERT INTO Foo(Id, Bar) VALUES (3, 'c');
GO

SET IDENTITY_INSERT Foo OFF;
GO

INSERT INTO Foo(Bar) VALUES ('d');
GO

Problem is that last insert - it complains that

A duplicate value cannot be inserted into a unique index. [ Table name = Foo,Constraint name = FooConstraint ]

This is unexpected. What am I doing wrong?

Solution

  • You need to reset the seed like this

    ALTER TABLE [Foo] ALTER COLUMN [Id] IDENTITY (4, 1)