Using Check Constraints with Column Sets

In our previous post on how to use columns sets, we saw how to design a simple table with a set of sparse columns representing 3 different groups of columns. Each group of columns represents a different kind of exercise. However, the way the table is designed in that post allows any of the sparse columns to have values, without enforcing any logical rules. SQL Server check constraints can be used to enforce rules so that when you insert a specific type of exercise, the correct columns are filled out.

Lord Cornwallis wished he could enforce some check constraints.

   Lord Cornwallis wished he could enforce check constraints.

This is the table we created in the previous post:

Next, we’ll extend it with some check constraints. We need to ensure when someone records a run, that they don’t insert treadmill or ski-related data. We also need to ensure when someone records a run, that they fill out all the columns specific to the “run” ExerciseType, and so-forth for the other exercise types.

The check constraint above enforces all rules for the sparse columns, effectively preventing NULL values for data we need for each of the ExerciseType values. Of course, further check constraints could be added to enforce limits on things like speed. For example, to ensure no one can enter a negative Best Segment Speed, one could add this constraint:

This constraint is only enforced for ExerciseType values of run.

Check constraint documentation from Microsoft is in their Docs collection.

This post is part of our series on T-SQL.

Ads by Google, Paying the Rent: