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?
