How do I create a multiple-table check constraint?
- by Zack Peterson
Please imagine this small 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
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?
Can I create a check constraint to
enforce that two overlapping shifts
are never staffed by the same
volunteer?
