实验七：数据库的完整性约束

一、实验目的

1. 掌握主键约束、外键约束及check约束的用法；
2. 掌握默认值约束和默认值对象的应用；
3. 掌握用触发器实现参照完整性的方法。

二、实验学时

2学时

三、实验要求

1. 了解约束、默认值；
2. 了解触发器的定义方法；
3. 掌握触发器的编写和运行方法

四、实验内容

1. 数据完整性。以实验数据库为基础数据，请使用T-SQL语句完成以下内容：

1. 将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;

alter table course表
add constraint cno_pk primary key(Cno)

2） 为表course中的字段cname添加唯一值约束；

alter table course表
add constraint A_cname unique(cname)

3） 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：

alter table sc
add constraint A_B foreign key (Sno)
references student(Sno)
alter table sc
add constraint C_D foreign key(Cno)
references Course(Cno)

 4）删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；

alter table sc
add constraint E_F foreign key(Sno)
references student(Sno)
on delete cascade

 5）修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；

alter table sc
add constraint G_H foreign key(sno)
references student(sno)
on update no action

6）修改course表cno字段值时，该字段在sc表中的对应值也应修改；

alter table sc
add constraint I_J foreign key(cno)
references course(cno)
on update cascade

7）删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

alter table sc
add constraint K_L foreign key(cno)
references course(cno)
on delete cascade

8）向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

alter table sc
add constraint M_N foreign key(sno)
references student(sno)
on update no action

9）定义stu数据库中student表中学生年龄值在16~25范围内；

alter table student
add constraint A_Sage
check(Sage between 16 and 25)

10）定义stu数据库student表中学生年龄值默认值为20；

alter table student
add constraint B_Sage default('20') for Sage

11）修改student表学生的年龄值约束可以为15~30范围内；

alter table student
drop constraint A_Sage
alter table student
add constraint C_Sage
check (sage between 15 and 30)

12）删除表course中字段cname的唯一值约束；

alter table course
drop constraint A_cname

2. 以实验数据库为基础数据，编写以下触发器并测试。（选做）

1) 为表course建立一个 INSERT触发器，当插入的新行中课程学分(ccredit)的值不是1~6时，就激活该出发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。

create trigger course_insert on course for insert
as 
declare @pr_ccredit char(4)
select @pr_ccredit=ccredit from inserted
if @pr_ccredit>7 or @pr_ccredit<1
begin
raiserror('不能插入',16,10)
rollback tran
end

2）为course表再创建一个UPDATE触发器，当更新了某门课程的课程号信息时，就激活该触发器级联更新sc表中相关的课程号信息，并使用PRINT语句返回一个提示信息。

create trigger cno_update on course
after update
as 
begin
declare @old_cno char(9),@new_cno char(9)
select @old_cno=cno from deleted
select @new_cno=cno from inserted
update sc set cno=@new_cno where cno=@old_cno
end

3）为student表创建DELECT触发器，在删除中的一条记录的同时将sc表中的相应记录也删除。

create trigger sno_delete on student
after delete
as 
begin
delete from sc where sno in(select sno from deleted)
end

 4）创建INSTEAD OF触发器，当向sc表中插入记录时，先检查sno列上的值在student中是否存在，如果存在执行插入操作，如果不存在则提示“该学生不存在”。

create trigger ttble on sc
instead of
insert
as
declare @pr_sno char(9)
select @pr_sno = sno from inserted
if @pr_sno not in(select sno from student)
begin 
raiserror('该学生不存在！',6,10)
rollback tran
end

五、实验分析与思考

1、本次实验过程中易出错的地方有哪些，如何解决。

        在设定约束的时候因为之前导入表的字段类型不一样导致出了很多问题。

解决：重新设计表后重新导入数据。

2、删除或更新数据表中记录时级联删除或更新其在相关外表中对应的记录，有哪些实现方法；

        删除包含主键值的行的操作，该值由其它表的现有行中的外键列引用。在级联删除中，还删除其外键值引用删除的主键值的所有行，通过触发器设置级联删除。

3、本次实验总结。

        本次实验创建触发器不会写，实验外还需要多多练习，定义约束目前练的比较熟练。但是还有一些完整性约束的定义会不太清楚，需要自己再去看书多多熟悉一下。

如有错误望批评指正！谢谢各位！