SQL Server Quirky Check Constraints

Recently at work, I found out curious table structure. I found out in one of the table, primary key had foreign key constraint. Well you will ask what's the strange thing about that? The strange thing is, this foreign key is referring to it self. To be clear, this is not a composite primary key, just a single column standard primary key. Investigation showed that it was done by mistake, no harm done, remove it and every one lived happily ever after.

But that got me thinking, why would SQL server allowed it? Does it has a use case? Curiously I went to find out about it more.

So code for this is something like below:

ALTER TABLE dbo.Employee WITH CHECK 

ADD CONSTRAINT FK_somename 

FOREIGN KEY (EmployeeId) REFERENCES dbo.Employee(EmployeeId)

So as would any one in this AI age would do, I have asked the question from 3 different AI models and all 3 basically gave the same answer.

As suspected, although valid syntax, this has no valid use case.

As a check constraint, this is utterly useless. Check constraint suppose to check value in foreign table already in source table. In this case of course value is already in source table because both are same and same column.

So will it fail when I insert a new value? No.

When inserting a brand new employee Id (e.g. 1010), value doesn't exists in the table yet. So FK constraint should fail. But it doesn't because order of operations happens in SQL server make sure it doesn't fail.

SQL Server's order of operations for an INSERT with FK constraints is roughly:

• Write the row into the table (tentatively, within the transaction)
• Then run the FK constraint check against the table's current state
• If the check passes → commit. If not → rollback.

In step 2 new id is already in the table, even though it is not committed. There fore FK rule pass. There fore, no rollback.

Is it ok to leave it if I found such a quirk? No. It is no harm removing it. Plus it add although tiny, overhead to the insert operation. With thousands of insert these tiny overheads can add upto become issues. So remove it if you found a one.

Why does SQL Server allows it?

Well my geuess is not by design decission, probably by accident when designing all constraint rules.

If you find any useful use case for this, please let me know in comments.

This interesting nature of check contraints made me thinking, we should re-visit all SQL server constraints and see what they are (just for fun and education). I might try to write a blog on that next month.