解释:
在一个表中,我们称之为GroupMember
,有一列叫做JoinDate
。 GroupMember
s 分配给具有DateFounded
的 Group
s。
Group
--------
GroupID identity int (PK)
DateFounded datetime
GroupMember
--------
GroupMemberID identity int (PK)
GroupID int (FK)
JoinDate datetime
我想对GroupMember
列JoinDate
有一个约束,这将阻止它像DateFounded
之前那样输入它Group
。我应该如何处理它?我可以使用检查约束吗?还是我需要函数/触发器?
谢谢
它可以完成,但它有点混乱(并且确实使用触发器,但仅用于支持插入,而不是强制执行实际约束(:
create table dbo.Group (
GroupID int identity not null primary key,
DateFounded datetime not null,
constraint UQ_Group_Founded UNIQUE (GroupID,DateFounded)
)
go
create table dbo._GroupMember (
GroupMemberID int identity not null primary key,
GroupID int not null references Group (GroupID)
JoinDate datetime not null,
_Founded datetime not null,
constraint FK_GroupMember_Founding FOREIGN KEY (GroupID,_Founded) references Group (GroupID,DateFounded),
constraint CK_GroupMember_NoTimeTravel CHECK (JoinDate >= _Founded)
)
go
create view dbo.GroupMember
with schemabinding
as
select GroupMemberID,GroupID,JoinDate
from dbo.GroupMember
go
create trigger T_GroupMember_I
on dbo.GroupMember
instead of insert
as
insert into dbo._GroupMember (GroupID,JoinDate,_Founded)
select i.GroupID,i.JoinDate,g.DateFounded
from inserted i inner join Group g on i.GroupID = g.GroupID
现在,您将视图GroupMember
视为原始GroupMember
表,并忽略_GroupMember
表。
继续具有要Group
的纯外键约束以及包含DateFounded
列的约束取决于您。如果你想允许在Group
中调整该日期,你应该将外键标记为ON UPDATE CASCADE
,它应该自动调整_GroupMember
中存储的值,如果检查约束被破坏,更新失败。