桂 林 理 工 大 学
实 验 报 告
班级 软件16-1班 学号 3162052051116 姓名 张识虔 同组实验者
实验名称 完整性 日期 2018年 07 月9 日
一、实验目的
1、掌握数据库约束的概念;
2、熟悉SQL SERVER 的完整性约束技术。
3、了解SQL SERVER 的违反完整性处理措施。
二、实验准备
1、了解数据库完整性约束的基本概述
2、了解SQL Server完整性约束技术。 包括实体完整性、参照完整性、用户定义完整性等。
3、了解主键(PRIMARY KEY)约束
4、了解外键(FOREIGN KEY)约束
5、了解唯一性(UNIQUE)约束
6、了解检查(CHECK)约束
7、了解DEFAULT 约束
8、了解允许空值约束
9、了解触发器的使用
三、实验内容
1.在前几次实验所使用的数据库中新建一个教师信息表,表名为Teacher,字段包括Tno(教师编号),Tname(姓名),Tsex(性别),Ttitle(职称),完整性包括:设定Tno为主键,Tname非空,Tsex的取值范围为“男”或“女”。
CREATE TABLE Teacher
(Tno CHAR(9) PRIMARY KEY,
Tname CHAR(20) not null,
Tsex CHAR(2),check(Tsex in('男','女')),
Ttitle CHAR(20)
);
2. 用insert语句插入如下记录,观察实验结果:
insert into Teacher values('2016001','王明','男','讲师')
insert into Teacher values('2016001','王英','女','助教')
insert into Teacher values('2016002','张方','a','讲师')
学号主键不允许有出现重复 因为有两个相同的学号所以出现错误
3. 对Teacher表增加一个约束,Ttitle属性的取值范围为('助教','讲师','副教授','教授'),并插入一条记录:
insert into Teacher values('2016003','刘阳','男','研究员'),观察实验结果。
CREATE TABLE Teacher
(Tno CHAR(9) PRIMARY KEY,
Tname CHAR(20) not null,
Tsex CHAR(2) check(Tsex in('男','女')),
Ttitle CHAR(20) check( Ttitle in('助教','讲师','副教授','教授'))
);
insert into Teacher values('2016003','刘阳','男','研究员');
![](https://i-blog.csdnimg.cn/blog_migrate/f973299e50d9321d9edb5a9288afbbd9.png)
研究员不在取值范围之内 所以出错
4. 为学生表Student增加一个约束,Ssex的取值范围为“男”或“女”。
alter table student
add constraint c1 check(Ssex in('男','女'));
5. 为选课表Sc增加一个约束,Grade的取值在0至100之间。
(提示,3,4,5是对表的结构增加约束定义,使用命令为:Alter table)
alter table Sc
add constraint c2 check(grade>=0 and grade<=100
6. 为选课表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表中的元组是否也做了相应的更改。
更改前
使用SQL语句
update Student
set Sno='201315123'
where Sname='王敏'
更改后
7. 为选课表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_ins ONsc
FOR INSERT
AS
BEGIN
DECLARE @bh char(9)
Select @bh=Inserted.snofrom Inserted
If not exists(select sno fromstudent
where student.sno=@bh)
Delete sc where sno=@bh
END
9. 创建一个修改触发器,当student表中的sno信息修改时,自动将SC表中的相应信息也修改。
(简单要求:修改仅为一条记录)
create trigger tri_student_upd
on student
for update
as
begin
Declare @oldid char(9),@newid char(9)
Select @oldid=sno fromdeleted
Select @newid=sno frominserted
Update sc set sno=@newidwhere sno=@oldid
end
update student
set sno='123456789'
where sno='201215121'