SQL SERVER – Identity and Constraint Confusion

Just another day my client of Comprehensive Database Performance Health Check sent me an email with a very interesting situation they were not able to insert any data into one of their tables. It was indeed a surprise for them and I personally had no idea what is going on with their server as I had no access to their data or schema. Let us learn about how identity and constraint confusion created problems for my clients.

Real-world Story

My client was very much confused as while everything was going fine suddenly they were not able to insert any data into their table. The very first thing which I did when I got online with my client is to check the schema of their table. Interestingly enough I found a check constraint that was on their Identity Column which was preventing them to insert the data into their table.

Upon talking with their team we quickly figured out that accidentally one of the developers created a constraint on the Identity Column instead of other columns in the table created all the trouble. After removing the constraint they were able to get back to business very quickly.

Sample Script – Constraint Confusion

I have created today’s sample script from this example. Let us add a little bit of fun to our demonstration.

First, create a table with an identity column and add identity on the ID column. The constraint says if the value of the ID is greater than 5 it will allow the insert into the table.

CREATE TABLE tblIdentity
(
ID INT IDENTITY(1,1),
Cols VARCHAR(100)
)
GO
ALTER TABLE tblIdentity 
	ADD CONSTRAINT CHECKID 
	CHECK (ID > 5)
GO

Next, try to insert 10 values into the table.

INSERT INTO tblIdentity (Cols)
VALUES ('RandomValues')
GO 10

You will notice that the first 5 attempts to insert the data into the table will fail as the ID column which is identity will prevent the insert into the table.

Right after that when the ID value reaches 6 it will allow the insert to take place.

You can also select from the table and see the data where the ID column contains values over 5.

SELECT *
FROM tblIdentity
GO

Well, that’s it. Sometimes small mistakes as such bring the business to halt. When you face any issue with your system, always ask yourself:

What was changed since everything was working fine?

The answer to the question may contain the solution to your problem.

Reference: Pinal Dave (https://blog.sqlauthority.com)