27,579
社区成员
发帖
与我相关
我的任务
分享
ALTER TABLE Dormitory
add constraint pk_Student_MaiRecord
Check(
DorID IN(Select DorID From Student stu where stu.SName=SName)
)
create table Student(DorID int,SName sysname)
create table Dormitory(DorID int not null,SName sysname not null,num int)
go
create function fn_Dormitory_primary
(@DorID int,@SName sysname)
returns bit
as
begin
if exists(select 1 from Student where DorID=@DorID and SName=@SName)
return 1
return 0
end
go
ALTER TABLE Dormitory
add constraint pk_Student_MaiRecord
Check( dbo.fn_Dormitory_primary(DorID,SName)=1)
go
insert into Student values(1,N'Roy')
insert into Dormitory values(1,N'Roy',10)--OK
insert into Dormitory values(2,N'Roy',10)--Error
insert into Dormitory values(1,N'Roy1',10)--Error
/*
消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 CHECK 约束"pk_Student_MaiRecord"冲突。该冲突发生于数据库"master",表"dbo.Dormitory"。
语句已终止。
*/