数据库实验五 完整性

一、实验目的：

1、掌握数据库约束的概念；

2、熟悉SQL SERVER 的完整性约束技术。

3、了解SQL SERVER 的违反完整性处理措施。

二、实验环境：

       Windows下的SQL

三、实验内容：

1.在前几次实验所使用的数据库中新建一个教师信息表，表名为Teacher，字段包括Tno(教师编号)，Tname(姓名)，Tsex(性别)，Ttitle(职称)，完整性包括：设定Tno为主键，Tname非空，Tsex的取值范围为“男”或“女”。

create tableTeacher(

Tno char(9) primary key,

Tname char(20) not null,

Tsex char(2) constraint C3 check(Tsex in('男','女')),

Ttitle char(20)

);

 

2. 用insert语句插入如下记录，观察实验结果：

insert into Teacher values('2016001','王明','男','讲师')

成功

 

insert into Teacher values('2016001','王英','女','助教')

违反了 PRIMARY KEY 约束“PK__Teacher__C450026D7AF6BC5D”。不能在对象“dbo.Teacher”中插入重复键。重复键值为 (2016001  )。

 

insert into Teacher values('2016002','张方','a','讲师')

INSERT 语句与 CHECK 约束"C3"冲突。该冲突发生于数据库"master"，表"dbo.Teacher",column 'Tsex'。

性别只能是男或女。

 

对Teacher表增加一个约束，Ttitle属性的取值范围为（'助教','讲师','副教授','教授'），并插入一条记录：

alter table Teacher

add constraint C1 check (Ttitle in('助教','讲师','副教授','教授'));

 

insert into Teacher values('2016003','刘阳','男','研究员')，观察实验结果。

INSERT 语句与 CHECK 约束"C1"冲突。该冲突发生于数据库"master"，表"dbo.Teacher",column 'Ttitle'。

 

为学生表Student增加一个约束，Ssex的取值范围为“男”或“女”。

alter table student

add constraint s1 check (ssex in('男','女'));

 

为选课表Sc增加一个约束，Grade的取值在0至100之间。

alter table sc

add constraint sc1 check (grade between 0 and 100);

 

（提示，3,4,5是对表的结构增加约束定义，使用命令为：Altertable）

为选课表Sc增加参照完整性及违约处理，定义sno为外键，与student表中的sno关联，当删除、更新student中的元组时，级联删除、更新Sc表中的相应元组，命令为：

alter table sc

add foreign key(sno) references student(sno)

on delete cascade

on update cascade

 

使用SQL语句，将Student表中姓名为”王敏”的元组，学号更改为“201315123”，然后查看SC表中的元组是否也做了相应的更改。

是。

 

为选课表Sc增加参照完整性及违约处理，定义cno为外键，与course表中的cno关联，当删除、更新course中的元组时，级联删除、更新Sc表中的相应元组，并进行验证。

alter table sc

add foreign key(cno) references course(cno)

on delete cascade

on update cascade

       是。

 

8. 建立一个触发器，当向sc表中添加数据时，如果添加的数据与student表中的数据不匹配（没有对应的学号），则将此数据删除。

create trigger sc_t onsc for insert

as

begin

    declare @bh char(5)

    select @bh=inserted.sno from inserted

    if not exists (select sno from student where student.sno=@bh)

        delete from sc where sno=@bh

end

9. 创建一个修改触发器，当student表中的sno信息修改时，自动将SC表中的相应信息也修改。

（简单要求：修改仅为一条记录）

（注：8，9的操作使用createtrigger命令，触发器创建成功后，自己使用insert语句，或者update语句进行验证，验证完毕，可以将触发器删除）

create trigger student_t on student for update

as

begin

       declare @bh char(9)

       declare @ah char(9)

       select@bh=inserted.sno from inserted

       select @ah=deleted.snofrom deleted

       if exists (select snofrom sc where sc.sno=@ah)

              update sc setsno=@bh where sno=@ah;

end

10. 用触发器实现教材例题5.21,5.22的功能。