如何创建多表检查约束? [英] How do I create a multiple-table check constraint?

问题描述

请想象这个小数据库...

Please imagine this small database...

删除了死掉的ImageShack链接-志愿者数据库图

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.

1. 我可以创建一个检查约束来 强制没有人员轮班 没有报名的志愿者 那个班次的事件?

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?

我可以创建一个检查约束来 强制执行两个重叠的班次 从来没有同一个人员 志愿者吗?

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

推荐答案

实现数据完整性的最佳位置是数据库.请放心，某些开发人员(无论有意还是无意)都会找到一种方法，让您将不一致的内容潜入数据库！

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')

这是表的定义:

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)
)

这篇关于如何创建多表检查约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！