实验八：数据库的完整性约束
一、实验目的
1. 掌握主键约束、外键约束及check约束的用法； 2. 掌握默认值约束和默认值对象的应用； 3. 掌握用触发器实现参照完整性的方法。
二、实验学时
2学时
三、实验要求
1. 了解约束、默认值； 2. 了解触发器的定义方法； 3. 掌握触发器的编写和运行方法 4. 完成实验报告。
四、实验内容
1. 数据完整性。以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将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 cname_uk unique(cname)
3) 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码
sno及表course的主码cno对应，实现如下参照完整性：
? 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记
录；
alter table sc add constraint sc_fk
foreign key(sno) references student(sno) on delete cascade
? 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条
记录，则拒绝修改；
alter table sc
add constraint sc_student_fk
foreign key(sno) references student(sno)
? 修改course表cno字段值时，该字段在sc表中的对应值也应修改；
alter table sc add constraint sc_fh
foreign key(sno) references student(sno) on update cascade
? 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段
对应的记录；
alter table sc
add constraint sc_course_fh
foreign key(cno) references course(cno) on delete cascade
? 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，
则拒绝插入；
alter table sc add constraint sc_fl
foreign key (sno)references student(sno)
4）定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
alter table student add constraint sno_ck
check (sno like '[1-9][0][0][0-9][0-9][0-9][0-9][0-9][0-9]')
5）定义stu数据库中student表中学生年龄值在16~30范围内； alter table student
add constraint sage_ck
check (sage>=16 and sage<=30)
6）定义stu数据库student表中学生年龄值默认值为20；
alter table student
add constraint sage_default
default 20 for sage
7） 修改student表学生的年龄值约束可以为15~35范围内；
alter table student drop constraint sage_ck go
alter table student add constraint sage_ck
check(sage>=15 and sage<=35)
8） 删除表course中字段cname的唯一值约束；
alter table course drop constraint cname_uk
2. 以实验数据库为基础数据，编写以下触发器并测试。
1) 为表course建立一个 INSERT触发器，当插入的新行中课程学分(ccredit)的值不是1~6时，就激活该出发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。
create trigger credit_insert on course for insert as
declare @xf tinyint
select @xf=ccredit from inserted if @xf not between 1 and 6 begin
RAISERROR('学分的取值只能是~6!',16,10) rollback transaction end
/*测试*/
insert into course values('11','C#程序设计',null,4)
insert into course values('12','C语言',null,8)
2）为course表再创建一个UPDATE触发器，当更新了某门课程的课程号信息时，就激活该触发器级联更新sc表中相关的课程号信息，并使用PRINT语句返回一个提示信息。
create trigger cno_update on course after update as
if update (cno)--检测课程号列是否被更新 begin
declare @old_cno char(3),@new_cno char(3)--声明变量获取更新前后的课程号的值
select @old_cno=deleted.cno,@new_cno=inserted.cno from deleted,inserted
PRINT'准备级联更新sc表中的课程号信息…'--级联更新sc表中相关成绩记录的课程号信息
update sc
set cno=@new_cno where cno=@old_cno
PRINT'已经级联更新sc表中原课程号'+@old_cno+'为'+@new_cno End
/*测试*/
update course set cno='30'
where cname='大学英语'
3）为student表创建DELECT触发器，在删除中的一条记录的同时将sc表