求一个Check约束修改

[Falleyes]

先看我的代码：

ALTER TABLE Dormitory
add constraint pk_Student_MaiRecord
Check(
	DorID IN(Select DorID From Student stu where stu.SName=SName)
)

需求是，我需要建立一个约束，DorID和SName是两列，表Student和表MailRecord都有的同名列，要将两个表的这两个列建立约束关系。
现在提示不能在这里使用子查询，且由于Student表中DorID和SName不是主键，所以无法建立外键关系。

除了触发器，还有别的方法吗？或者这个SQL语句怎样修改可以达到目的？

[中国风]

用函数代替子查询检查

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"。
语句已终止。
*/

[Falleyes]

引用 1 楼 roy_88 的回复:

用函数代替子查询检查

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"。
语句已终止。
*/

嗯，受教了，结贴！