How are my SQL Server constraints being bypassed?

We have found a handful of rows in our DB that violate an active constraint. How is this possible?

The constraint is active, as we can't just manually add a row that bypasses this constraint. However, when we run CHECKCONSTRAINTS(Files), we find that it has been bypassed on a handful of occasions during our test runs. The rows in question were all created within a half-second of one another, suggesting some kind of race condition.

Here's the constraint being applied to the table. The rule is meant to ensure name uniqueness in a given parent folder:

ALTER TABLE Files ADD CONSTRAINT UniqueNameInParentFolder CHECK
    CheckUniqueNameInFolder(ParentFoldersID, Name) = 1;

This constraint calls a function that looks like this:

-- first check for the new name in the Folders table
IF ((SELECT COUNT(*) FROM Folders 
     WHERE ParentFoldersID = @FoldersID AND Name = @Name) = 0)
BEGIN 
    -- then check for it in the Files table
    IF ((SELECT COUNT(*) FROM Files 
         WHERE ParentFoldersID = @FoldersID AND Name = @Name) <= 1)
        RETURN 1
END
RETURN 0

Individual rows are added inside transactions, so I'm having a hard time understanding how duplicate rows are sneaking past this constraint.

Check constraints based on UDFs are rubbish. Concurrency, RBAR, isolation etc as you've found out. Some links:

-
Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov

-
Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson

-
Be careful with constraints calling UDFs by Tibor Karaszi

The safest way for SQL Server in this case would be to use standard constraints such as unique and foreign keys. I can't see why you check the folders table for a constraint on the files table though.

To prevent a file and a folder having the same name in a given parent folder only, use an indexed view. Duplicate files or duplicate folders requires table level uniqueness.

CREATE VIEW CheckUnique
WITH SCHEMABINDING
AS
SELECT fo.ParentFoldersID, fo.Name
FROM
   Folders fo
   JOIN
   File fi ON fo.ParentFoldersID  = fi.ParentFoldersID AND fo.Name = fi.Name
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_CheckUnique ON CheckUnique (ParentFoldersID, Name)
GO

Or a trigger.

But never a UDF in a check constraint

StackExchange Database Administrators Q#12779, answer score: 23