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 check constraints.
This is the table we created in the previous post:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
IF OBJECT_ID(N'dbo.ExerciseHistory', N'U') IS NOT NULL DROP TABLE dbo.ExerciseHistory; GO CREATE TABLE dbo.ExerciseHistory ( ExerciseHistoryID int NOT NULL CONSTRAINT ExerciseHistory_pk PRIMARY KEY CLUSTERED IDENTITY(1,1) , EntryDate datetime NOT NULL CONSTRAINT ExcerciseHistory_EntryDate_DF DEFAULT (GETDATE()) , ExerciseType varchar(9) NOT NULL CONSTRAINT ExerciseType_ck CHECK (ExerciseType IN ('run', 'treadmill', 'ski')) , RunDistance decimal(5,2) SPARSE , RunRouteName varchar(50) SPARSE , RunBestSegmentTime decimal(5,2) SPARSE , TreadmillProgram varchar(15) SPARSE , TreadmillMaxSpeed decimal(5,2) SPARSE , SkiFacilityName varchar(50) SPARSE , SkiRunNumber int SPARSE , SkiSlopeRating varchar(15) SPARSE , ExerciseDetails xml COLUMN_SET FOR ALL_SPARSE_COLUMNS ); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER TABLE dbo.ExerciseHistory ADD CONSTRAINT ExcersiseHistory_Consistency_Check CHECK (( (ExerciseType <> 'run' AND RunDistance IS NULL AND RunRouteName IS NULL AND RunBestSegmentTime IS NULL) OR (ExerciseType = 'run' AND RunDistance IS NOT NULL AND RunRouteName IS NOT NULL AND RunBestSegmentTime IS NOT NULL) ) AND ( (ExerciseType <> 'treadmill' AND TreadmillProgram IS NULL AND TreadmillMaxSpeed IS NULL) OR (ExerciseType = 'treadmill' AND TreadmillProgram IS NOT NULL AND TreadmillMaxSpeed IS NOT NULL) ) AND ( (ExerciseType <> 'ski' AND SkiFacilityName IS NULL AND SkiRunNumber IS NULL AND SkiSlopeRating IS NULL) OR (ExerciseType = 'ski' AND SkiFacilityName IS NOT NULL AND SkiRunNumber IS NOT NULL AND SkiSlopeRating IS NOT NULL) )); |
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:
|
1 2 3 |
ALTER TABLE dbo.ExerciseHistory ADD CONSTRAINT ExerciseHistory_run_best_segment_time CHECK ((ExerciseType = 'run' AND (RunBestSegmentTime >= 0 AND RunBestSegmentTime <= 100))); |
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.