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.