Are unique filtered indexes considered an antipattern for enforcing constraints?
2
votes
3
answers
972
views
I have a table, such as the following. Let's called it
Fools
.
| FoolID | FoolValue | IsActiveFoolValue |
|--------|-----------|-------------------|
| 1 | Ultra Foolish | 0 |
| 1 | Super Foolish | 1 |
| 2 | Quite Foolish | 1 |
A business rule is that for each FoolID
, we can only have IsActiveFoolValue
be 1
on one row. I want some sort of key or constraint to enforce this. However, to my knowledge, keys and constraints cannot do this. The only tool is a unique index like
CREATE UNIQUE NONCLUSTERED INDEX OnlyOneActiveFoolValuePerFoolId
ON Fools(FoolID)
WHERE IsActiveFoolValue = 1
is this considered an antipattern? Indexes aren't supposed to be constraints. They're supposed to be performance tools. Constraints should be enforced by keys, constraints, and maybe triggers. If it is considered an antipattern, what disadvantages does it bring and what alternatives exist?
I'm assuming SQL Server 2019, because some features of unique filtered indexes did not exist in very old versions and I want to keep my options open for any modern alternatives.
Asked by J. Mini
(1237 rep)
Jul 13, 2024, 06:33 PM
Last activity: Jul 14, 2024, 09:18 PM
Last activity: Jul 14, 2024, 09:18 PM