How do I create a multiple-table check constraint?

Question

Please imagine this small database...

Diagram

removed dead ImageShack link - volunteer database diagram

Tables

Volunteer     Event         Shift         EventVolunteer =========     =====         =====         ============== Id            Id            Id            EventId Name          Name          EventId       VolunteerId Email         Location      VolunteerId Phone         Day           Description Comment       Description   Start                             End 

Associations

Volunteers may sign up for multiple events.
Events may be staffed by multiple volunteers.

An event may have multiple shifts.
A shift belongs to only a single event.

A shift may be staffed by only a single volunteer.
A volunteer may staff multiple shifts.

Check Constraints

1. Can I create a check constraint to enforce that no shift is staffed by a volunteer that's not signed up for that shift's event?

2. Can I create a check constraint to enforce that two overlapping shifts are never staffed by the same volunteer?

Answer 1

The best place to enforce data integrity is the database. Rest assured that some developer, intentionally or not, will find a way to sneak inconsistent stuff into the database if you let them!

Here's an example with check constraints:

CREATE FUNCTION dbo.SignupMismatches() RETURNS int AS BEGIN RETURN (     SELECT count(*)     FROM Shift s     LEFT JOIN EventVolunteer ev     ON ev.EventId = s.EventId     AND ev.VolunteerId = s.VolunteerId     WHERE ev.Id is null ) END go ALTER TABLE Shift ADD CONSTRAINT chkSignup CHECK (dbo.SignupMismatches() = 0); go CREATE FUNCTION dbo.OverlapMismatches() RETURNS int AS BEGIN RETURN (     SELECT count(*)     FROM Shift a     JOIN Shift b     ON a.id <> b.id     AND a.Start < b.[End]     AND a.[End] > b.Start     AND a.VolunteerId = b.VolunteerId ) END go ALTER TABLE Shift ADD CONSTRAINT chkOverlap CHECK (dbo.OverlapMismatches() = 0); 

Here's some tests for the new data integrity checks:

insert into Volunteer (name) values ('Dubya') insert into Event (name) values ('Build Wall Around Texas')  -- Dubya tries to build a wall, but Fails because he's not signed up insert into Shift (VolunteerID, EventID, Description, Start, [End])      values (1, 1, 'Dunbya Builds Wall', '2010-01-01', '2010-01-02')  -- Properly signed up?  Good insert into EventVolunteer (VolunteerID, EventID)      values (1, 1) insert into Shift (VolunteerID, EventID, Description, Start, [End])      values (1, 1, 'Dunbya Builds Wall', '2010-01-01', '2010-01-03')  -- Fails, you can't start the 2nd wall before you finished the 1st insert into Shift (VolunteerID, EventID, Description, Start, [End])      values (1, 1, 'Dunbya Builds Second Wall', '2010-01-02', '2010-01-03') 

Here are the table definitions:

set nocount on if OBJECT_ID('Shift') is not null     drop table Shift if OBJECT_ID('EventVolunteer') is not null     drop table EventVolunteer if OBJECT_ID('Volunteer') is not null     drop table Volunteer if OBJECT_ID('Event') is not null     drop table Event if OBJECT_ID('SignupMismatches') is not null     drop function SignupMismatches if OBJECT_ID('OverlapMismatches') is not null     drop function OverlapMismatches  create table Volunteer (     id int identity primary key ,   name varchar(50) ) create table Event (     Id int identity primary key ,   name varchar(50) ) create table Shift (     Id int identity primary key ,   VolunteerId int foreign key references Volunteer(id) ,   EventId int foreign key references Event(id) ,   Description varchar(250) ,   Start datetime ,   [End] datetime ) create table EventVolunteer (     Id int identity primary key ,   VolunteerId int foreign key references Volunteer(id) ,   EventId int foreign key references Event(id) ,   Location varchar(250) ,   [Day] datetime ,   Description varchar(250) ) 

Answer 2

Question 1 is easy. Just have your Shift table refer directly to EventVolunteer table and you are all set