You cannot use CHECK constraints to compare columns in different
rows or in different tables, unless you wrap such functionality in scalar UDFs
invoked form a CHECK constraint. What if you actually need to compare columns
in different rows or in different tables to enforce a business rule? For
example, suppose that you know working hours of a doctor, and you want to make
sure that all appointments fit within working hours? Of course, you can use a trigger
or a stored procedure to implement this business rule, but neither a trigger
nor a stored procedure can give you a 100% guarantee that all your data is
clean – someone can disable or drop your trigger, enter some dirty data, and re-enable
or recreate your trigger. Also someone can directly modify your table bypassing
stored procedures. Either way you can end up with data violating your business
rule without knowing about it.
Let me demonstrate how to implement this business rule using
only FK and CHECK constraints – that will guarantee that all the data satisfies
the business rule as long as all the constraints are trusted.
Note: for more about trusted constraints read a post by SQL
Server MVP Hugo Kornelis.
The table storing working hours has one additional
constraint which will be used to make sure that all appointments fit within
working hours, as follows:
CREATE Table Data.WorkingHours(WorkingHoursID INT NOT NULL,
StartedAt DATETIME
NOT NULL,
FinishedAt DATETIME
NOT NULL,
ProfessionalsName VARCHAR(50),
CONSTRAINT
PK_WorkingHours PRIMARY KEY(WorkingHoursID),
-- The purpose of UNQ_WorkingHours is as follows:
-- a FOREIGN KEY constraint from a child table will refer to it.
CONSTRAINT
UNQ_WorkingHours UNIQUE(WorkingHoursID, StartedAt,
FinishedAt),
CONSTRAINT
CHK_WorkingHours_ValidWindow CHECK(StartedAt <
FinishedAt)
);
GO
SET NOCOUNT ON;
INSERT Data.WorkingHours(WorkingHoursID,
ProfessionalsName,
StartedAt,
FinishedAt)
SELECT 1, 'Jane He', '20061025 08:00AM', '20061025 05:00PM' UNION ALL
SELECT 2, 'Jane He', '20061026 08:30AM', '20061026 05:00PM' UNION ALL
SELECT 3, 'Jane He', '20061027 08:30AM', '20061027 05:00PM';
GO
Now I am going to store the beginning and ending of working
hours (that is StartedAt and FinishedAt columns) along with every appointment,
so that I can use a CHECK constraint to compare them with the beginning and
ending of appointments, as follows:
CREATE Table Data.Appointments
( WorkingHoursID INT
NOT NULL,
StartedAt DATETIME
NOT NULL,
FinishedAt DATETIME
NOT NULL,
WorkingHourStartedAt DATETIME NOT NULL,
WorkingHourFinishedAt DATETIME NOT NULL,
CONSTRAINT
FK_Appointments_WorkingHours
FOREIGN
KEY(WorkingHoursID, WorkingHourStartedAt,
WorkingHourFinishedAt)
REFERENCES
Data.WorkingHours(WorkingHoursID, StartedAt,
FinishedAt) ON UPDATE CASCADE,
CONSTRAINT
CHK_Appointments_ValidWindow CHECK(StartedAt <
FinishedAt),
CONSTRAINT
CHK_TimesInRange
CHECK((StartedAt BETWEEN
WorkingHourStartedAt AND WorkingHourFinishedAt)
AND (FinishedAt BETWEEN
WorkingHourStartedAt AND WorkingHourFinishedAt))
);
Clearly storing two columns from the parent table in the
child one is denormalization. However, my FOREIGN KEY constraint guarantees
that the columns from the parent table always match the corresponding ones in
the child one. On top of that, the ON UPDATE CASCADE
clause allows me to modify the values in the parent table, and these changes
are propagated into the child one automatically. I will demonstrate it soon.
The following inserts succeed, because the appointments fit into business hours:
INSERT Data.Appointments(WorkingHoursID,
StartedAt,
FinishedAt,
WorkingHourStartedAt,
WorkingHourFinishedAt)
SELECT 1, '20061025 09:00AM', '20061025 05:00PM', '20061025 08:00AM', '20061025 05:00PM' UNION ALL
SELECT 2, '20061026 08:30AM', '20061026 01:00PM', '20061026 08:30AM', '20061026 05:00PM' UNION ALL
SELECT 3, '20061027 10:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM';
You can change appointments and working hours as long as appointments
still fit, for example:
UPDATE Data.Appointments SET
StartedAt = '20061025
08:30AM'
WHERE
WorkingHoursID = 1;
UPDATE Data.WorkingHours SET
StartedAt = '20061027
09:31AM'
WHERE
WorkingHoursID = 3;
UPDATE Data.WorkingHours SET
FinishedAt = '20061026
04:31PM'
WHERE
WorkingHoursID = 2;
Note that the changes in WorkingHours table were propagated
into the child table automatically:
SELECT StartedAt,
FinishedAt,
WorkingHourStartedAt,
WorkingHourFinishedAt
FROM Data.Appointments;
StartedAt FinishedAt WorkingHourStartedAt WorkingHourFinishedAt
-----------------------
----------------------- ----------------------- -----------------------
2006-10-25 08:30:00.000
2006-10-25 17:00:00.000 2006-10-25 08:00:00.000 2006-10-25 17:00:00.000
2006-10-26 08:30:00.000
2006-10-26 13:00:00.000 2006-10-26 08:30:00.000 2006-10-26 16:31:00.000
2006-10-27 10:30:00.000
2006-10-27 13:00:00.000 2006-10-27 09:31:00.000 2006-10-27 17:00:00.000
However, you cannot extend your appointment beyond working
hour nor you can shorten your working hours so that some appointment no longer
fits:
--
all fail: range not completely inside the parent range
INSERT Data.Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM');
INSERT Data.Appointments VALUES(3, '20061027 10:30AM', '20061027 08:00PM', '20061027 08:30AM', '20061027 05:00PM');
--
fails because there is an appoinment starting at 10:30
UPDATE Data.WorkingHours SET StartedAt = '20061027 10:31AM' WHERE
WorkingHoursID = 3;
Msg 547, Level 16, State 0,
Line 2
The INSERT statement
conflicted with the CHECK constraint "CHK_TimesInRange". The conflict
occurred in database "Test", table "Data.Appointments".
The statement has been
terminated.
Msg 547, Level 16, State 0,
Line 3
The INSERT statement
conflicted with the CHECK constraint "CHK_TimesInRange". The conflict
occurred in database "Test", table "Data.Appointments".
The statement has been
terminated.
Msg 547, Level 16, State 0,
Line 5
The UPDATE statement
conflicted with the CHECK constraint "CHK_TimesInRange". The conflict
occurred in database "Test", table "Data.Appointments".
The statement has been
terminated.
In many cases constraints also perform better than triggers,
but this is another story…
In the next post I am discussing running totals