如何创建多表检查约束?

执行数据完整性的最佳位置是数据库。请放心,有些开发人员,不管是有意还是无意,都会找到一种方法,如果您允许的话,可以将不一致的东西偷偷地添加到数据库中!

下面是带有检查约束的示例:

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

以下是新数据完整性检查的一些测试:

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

问题1很容易。把你的轮班表直接交给活动志愿者,你就准备好了。

我要做的是在eventsvolunteid表上有一个标识列,它会自动递增,在eventid和volunteid对上有一个唯一的约束。使用eventvolunteid(标识)作为班次表的外键。这将强制您非常简单地使用您想要的约束,同时稍微规范化您的数据。

我知道这不是你一般问题的答案,但我认为这是解决你具体问题的最佳方案。

编辑:

我本该把这个问题读清楚的。这个解决方案将阻止一个志愿者在同一个活动中做两个轮班,即使他们没有重叠。也许将轮班开始和结束时间移动到事件志愿者,并在该表上对时间进行检查限制就足够了,尽管这样,轮班表之外的轮班数据听起来并不直观。

有一种方法可以通过使用触发器来实现,我不建议这样做。我建议不要将您的业务逻辑放在数据库级别。数据库不需要知道谁,在某个时间安排了一个班次。这个逻辑应该放在您的业务层中。我建议使用存储库构建模式。ScottGutherie在他的MVC1.0书中有一个非常好的章节描述了这一点(链接如下)。

http://weblogs.asp.net/scottgu/archive/2009/03/10/free-asp-net-mvc-ebook-tutorial.aspx