数据库完整性管理实验

一、 实验目的

1、 理解数据库完整性约束机制；

2、 掌握创建、修改和删除完整性约束的方法；

3、 掌握创建和删除触发器的方法。

二、 实验内容

（一）附加上次实验所创建的数据库“db_Library”，并回顾该数据库的数据表信息。

（二）约束的创建和管理

1、使用 SQL 语句创建图书表（tb_booknew），要求为各字段选择合适的数据类型及名称，其中 “图书编号”字段为主码；“类别编号”字段为外码，删除图书类别信息表中记录时级联的删除图书 信息表中对应的记录；书名不允许空值；库存数限制的 25 到 100 之间。

2、使用 SQL 语句创建借阅表（tb_borrownew)，要求为各字段选择合适的数据类型及名称，其 中“图书编号”和“读者编号”的组合值为主码。

3、使用 SQL 语句为图书表（tb_booknew）中书名字段增加唯一性约束。

4、使用 SQL 语句将第 1 题的库存数限制修改为 30 到 80 之间。

5、使用 SQL 语句删除第 3 题增加的约束。

（三）触发器的创建和管理

1、使用 SQL 语句创建一个名为“借阅信息插入修改_TRIG”的触发器，要求在“借阅信息表”

中插入或修改记录时触发该触发器，检查“归还日期”字段修改是否超过 3 个月，如果超过给出相

应提示，不超过给出相应提示（“插入记录成功”或“修改记录成功”）。对“借阅信息表”进行插入

或修改操作，验证触发器的执行情况。

2、使用 SQL 语句创建一个插入、更新类型的触发器“图书信息_TRIG”，当“图书信息表”中

插入或修改记录时，触发该触发器，检查库存册数是否大于 0，若不大于 0，则撤销插入和修改操作。

在“借阅信息表”中插入或修改一条记录，给定的库存册数值为-1，验证触发器的执行情况

3、使用 SQL 语言删除“借阅信息插入修改_TRIG”触发器。

go
create table tb_booknew
(
图书编号 int primary key,
类别编号 int,
书名 varchar(20) not null,
作者 varchar(20),
出版社 varchar(20),
定价 float,
库存数 int check(库存数 between 25 and 100)
foreign key (类别编号) references tb_booktype(类别编号)
on delete cascade
)

go 
create table tb_borrownew
(
图书编号 varchar(20),
读者编号 varchar(20),
借阅日期 date,
归还日期 date,
primary key(图书编号,读者编号)
)

alter table tb_booknew add constraint c unique(书名)

alter table tb_booknew add constraint c2 check(库存数 between 30 and 80)

alter table tb_booknew drop constraint c

alter table tb_booknew drop constraint c2,c3
go
insert into dbo.tb_booknew values(10001,3,'数据库管理','王珊','高等教育出版社',35.50,10)
insert into dbo.tb_booknew values(10002,3,'软件测试','贺平','机械工业出版社',24.60,5)
insert into dbo.tb_booknew values(10003,3,'c++程序设计','谭浩强','清华大学出版社',30.00,8)
insert into dbo.tb_booknew values(10004,4,'红楼梦','曹雪芹','人民文学出版社',70.00,5)
insert into dbo.tb_booknew values(10005,4,'西游记','罗贯中','人民文学出版社',60.00,8)
insert into dbo.tb_booknew values(10006,4,'红与黑','司汤达','人民文学出版社',50.00,5)
insert into dbo.tb_booknew values(10007,1,'高等数学','李翼','清华大学出版社',28.00,4)
insert into dbo.tb_booknew values(10008,8,'有机化学','张翔','高等教育出版社',29.00,5)
insert into dbo.tb_booknew values(10009,2,'大学英语','王琳','高等教育出版社',25.00,10)
insert into dbo.tb_booknew values(10010,2,'英语教程','王琳','高等教育出版社',25.00,5)

go 
insert into dbo.tb_borrownew values(10002,'R10003','2014-09-20','2014-10-20')
insert into dbo.tb_borrownew values(10003,'R10003','2014-09-20','2014-10-20')
insert into dbo.tb_borrownew values(10004,'R10003','2014-09-30','2014-10-30')
insert into dbo.tb_borrownew values(10009,'R10003','2014-09-30','2014-10-30')
insert into dbo.tb_borrownew values(10009,'R10007','2014-05-20','2014-06-20')
insert into dbo.tb_borrownew values(10010,'R10007','2014-05-20','2014-06-20')
insert into dbo.tb_borrownew values(10009,'R10009','2014-05-30','2014-06-30')
insert into dbo.tb_borrownew values(10010,'R10009','2014-05-22','2014-06-22')
insert into dbo.tb_borrownew values(10002,'R10009','2014-05-22','2014-06-22')
insert into dbo.tb_borrownew values(10003,'R10009','2014-05-30','2014-06-30')
go

--1、使用 SQL 语句创建一个名为“借阅信息插入修改_TRIG”的触发器，要求在“借阅信息表” 
--中插入或修改记录时触发该触发器，检查“归还日期”字段修改是否超过 3 个月，如果超过给出相 
--应提示，不超过给出相应提示（“插入记录成功”或“修改记录成功”）。对“借阅信息表”进行插入 
--或修改操作，验证触发器的执行情况。 
create trigger 借阅信息插入修改_TRIG
on tb_borrownew after
insert,update
as
begin
declare @归还日期 date
declare @借阅日期 date
select @归还日期=归还日期,@借阅日期=借阅日期 from inserted
if(DATEDIFF(M,@借阅日期,@归还日期)>3)
begin print('数据插入失败'); end
else
begin if (exists(select *from inserted) and not exists(select *from deleted))
begin print('插入成功') end
if(exists(select *from inserted) and exists(select *from deleted))
begin print('修改成功') end
end
end

insert into tb_borrownew values('10010','R10011','2014-04-22','2014-09-22')

insert into tb_borrownew values('10011','R10013','2014-04-22','2014-05-22')

update tb_borrownew set 归还日期='2014-06-22' where 读者编号='R10013'

--2、使用 SQL 语句创建一个插入、更新类型的触发器“图书信息_TRIG”，当“图书信息表”中 插入或修改记录时，
--触发该触发器，检查库存册数是否大于 0，若不大于 0，则撤销插入和修改操作。
--在“借阅信息表”中插入或修改一条记录，给定的库存册数值为-1，验证触发器的执行情况 
go
create trigger 图书信息_TRIG
on tb_booknew
after insert,update
as
begin
declare @库存数 int
select @库存数=库存数 from inserted
if @库存数<0
begin print'库存数小于0' rollback end
else if @库存数>0
begin   print('数据插入成功')end
end

insert into tb_booknew values('10011','2','英语语法','何','华出版社',23.5,-1);
insert into tb_booknew values('10011','2','英语语法','何','华出版社',23.5,8);

drop trigger 借阅信息插入修改_TRIG