日期:2014-05-17 浏览次数:20553 次
use master;
go
if object_id('customer')is not null drop table customer;
if object_id('room')is not null drop table room;
go
create table customer(
id int,
[name] varchar(100),
room_id int
)
create table room(
id int,
roomid int,
num int
)
--其中customer里的room_id和room里的id主外键关联
insert into customer(id,[name],room_id)
select 1,'jack',2
union all select 2,'tom',2
union all select 3,'lily',4
insert into room(id,roomid,num)
select 2,101,4
union all select 3,102,6
--room中id=2中有两条关联数据jack和tom
--id=3中有一条关联数据lily。
--
--那我怎样对其关联的字段条数进行限制
--也就是我如果想在room中id= 2中只能让其在customer中插入4个关联数据怎么做?
--insert into customer
;with t as(select 4 id,'mike' [name] ,2 room_id,(select num from room a
where a.id = 2)num)
insert customer(id,[name],room_id)
select id,[name],room_id from t
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'
;with t as(select 5 id,'master' [name] ,2 room_id,(select num from room a
where a.id = 2)num)
insert customer(id,[name],room_id)
select id,[name],room_id from t
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'
;with t as(select 5 id,'ccyou' [name] ,2 room_id,(select num from room a
where a.id = 2)num)
insert customer(id,[name],room_id)
select id,[name],room_id from t
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'