Check Constraint on Identity Column: Why Inserts Fail

Adding a check constraint on identity column values is a mistake that makes inserts fail for no visible reason. The identity moves on whether or not the row is accepted. This post shows the failure and a query that finds these constraints.

The Mistake in a Real Table

One team found that their table suddenly refused every insert. A developer had put a check constraint on the identity column instead of the column they meant. The first clue was the question to ask in any outage: what changed since it last worked?

An identity column hands out the next number by itself. A check constraint tests every new row against a rule. The two features do not talk to each other, and that gap causes the trouble. The identity does not wait to learn whether the row will pass.

The demo creates a database named IdentityCheckDemo with one table of seed trays. The first constraint is the mistake: it demands that TrayID is greater than 5. The second is the rule the developer wanted, on Quantity. The third mixes both columns. Run it on a test server.

IF DB_ID(N'IdentityCheckDemo') IS NULL CREATE DATABASE IdentityCheckDemo;
GO
USE IdentityCheckDemo;
GO
DROP TABLE IF EXISTS dbo.SeedTrays;
CREATE TABLE dbo.SeedTrays (
    TrayID   int IDENTITY(1,1) NOT NULL,
    Label    varchar(40) NOT NULL,
    Quantity int NOT NULL
);
ALTER TABLE dbo.SeedTrays ADD CONSTRAINT CK_SeedTrays_TrayID CHECK (TrayID > 5);
ALTER TABLE dbo.SeedTrays ADD CONSTRAINT CK_SeedTrays_Quantity CHECK (Quantity >= 0);
ALTER TABLE dbo.SeedTrays ADD CONSTRAINT CK_SeedTrays_Both CHECK (TrayID > 0 AND Quantity >= 0);

Why Every Insert Fails

The next script tries to insert ten rows. The GO 10 line repeats the batch ten times.

INSERT INTO dbo.SeedTrays (Label, Quantity) VALUES ('Basil', 12);
GO 10
SELECT TrayID, Label, Quantity FROM dbo.SeedTrays ORDER BY TrayID;
SELECT IDENT_CURRENT('dbo.SeedTrays') AS CurrentIdentity;

The first five attempts fail with Msg 547, a level 16 error. SQL Server hands out the next identity value, tests the new row against the constraint, and rejects it. The message names the constraint and the column.

The INSERT statement conflicted with the CHECK constraint "CK_SeedTrays_TrayID". The conflict occurred in database "IdentityCheckDemo", table "dbo.SeedTrays", column 'TrayID'.
The statement has been terminated.

The sixth attempt gets TrayID 6, which passes. So the table ends up with five rows.

IDENT_CURRENT returns 10. Each failed insert used up one value, and the identity never gives a value back. A rolled-back insert behaves the same way. That is why a table can show gaps with no deletes at all.

Find Every Check Constraint on an Identity Column

A check constraint on identity column values has no good use, because the system generates the value. The query below finds them in the current database. It joins the constraints to the identity columns. It also searches the constraint text. A check that names two columns is stored with column id 0 in parent_column_id. The join alone would miss it.

SELECT OBJECT_NAME(cc.parent_object_id) AS TableName,
       c.name AS ColumnName,
       cc.name AS ConstraintName,
       cc.definition
FROM sys.check_constraints AS cc
JOIN sys.identity_columns AS ic ON ic.object_id = cc.parent_object_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE cc.parent_column_id = ic.column_id
   OR cc.definition LIKE N'%[[]' + c.name + N']%'
ORDER BY cc.name;

The constraint on Quantity is not listed, which is correct. CK_SeedTrays_Both is a harmless rule that happens to mention the identity column, so read each row before you drop anything. Add cc.create_date to the column list when you want to know which constraint arrived last.

Fix It and Look at the Gap

Drop the wrong constraint, then insert one more row. The new row gets TrayID 11, not 6, because the counter kept going.

ALTER TABLE dbo.SeedTrays DROP CONSTRAINT CK_SeedTrays_TrayID;
INSERT INTO dbo.SeedTrays (Label, Quantity) VALUES ('Mint', 8);
SELECT TrayID, Label FROM dbo.SeedTrays WHERE Label = 'Mint';

You could argue that the rule TrayID > 5 is legitimate. A team can have a reason to keep the low numbers free for system rows. Then say so in the table definition and start the identity at 6, as in IDENTITY(6,1). The rule then lives in the definition and no insert fails. The CHECK constraint is not needed any more, so drop it.

A Constraint That Belongs There

The constraint on Quantity is the useful kind. It guards a value that people type. This insert breaks it on purpose, and the next one is valid.

INSERT INTO dbo.SeedTrays (Label, Quantity) VALUES ('Dill', -4);
GO
INSERT INTO dbo.SeedTrays (Label, Quantity) VALUES ('Chive', 5);
SELECT TrayID, Label FROM dbo.SeedTrays WHERE Label = 'Chive';

The first insert fails with Msg 547 again, this time for CK_SeedTrays_Quantity and column Quantity. The rule fired for a good reason, and it still used up an identity value. TrayID 12 is gone, so Chive gets 13.

Every rejected row costs one value, whatever the constraint. A bad constraint on the identity column makes that cost visible. A good constraint on a data column hides it. Neither one is a reason to worry about the gaps. In this demo, the number of rejected rows is exactly the size of the gap.

What to Remember

Put constraints on the data you type, not on a value the system makes. When inserts start failing, read the constraint name in Msg 547 first. It tells you which rule fired and which column it guards.

Do not try to repair gaps in an identity column. They are normal, and the cure is worse than the gap. When you finish the demo, drop the database. Every check constraint on identity column values that you find is worth a second look before you remove it.

USE master;
GO
DROP DATABASE IdentityCheckDemo;

A constraint is not a rule for the system, it is a rule for the data you type.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.