This technique by Troy Ketsdever over at SQL Server Central is a neat piece of work. Definitely read the full article, but I’ll try to summarize here:

Sometimes you want to enforce a multi-column constraint on a join table that says “this row is invalid and should not be allowed”. In Troy’s example here’s the constraint to be applied to a relationship between VETS and PETS:

Only VETS with a RATING of 'Senior' can see PETS with a DISPOSITION of 'Difficult'.

You’d can easily map the relationship using the VETS_X_PETS join table and two FKs back to the source table, but how to enforce the constraint? Well, thinking about it, you might use an INSERT/UPDATE trigger to enforce the rule. Or, you might enforce the rule in middle-tier business logic code. However, Ketsdever has a very neat technique involving indexed views, explained in three parts:

1. Create a view that explicitly selects rows that meet the “disallowed” constraint.
2. Cross-join the new view on a helper “digits” table with the values 0…9.
3. Enforce a unique index on the new view.

Now, when you try to add a row to the VETS_X_PETS table that creates a violation of the constraint, the unique index throws an error, disallowing the insert or update.

This took me a few minutes of pondering before I finally grasped the fundamental concept, but is a really neat technique and one that I’ll be adding to my arsenal. You should read the original article to fully appreciate it.