Wonderful SQL Server Technique for Enforcing Complex Rules Using Indexed Views

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.

Advertisements

0 Responses to “Wonderful SQL Server Technique for Enforcing Complex Rules Using Indexed Views”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: