实验八 数据库的完整性约束 下载本文

实验八:数据库的完整性约束

一、实验目的

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表