实验四 数据库完整性

1、实验四：数据库的完整性一、实验目的1. 熟悉主键约束、外键约束、空值、惟一约束的使用方法；2. 掌握默认值约束和默认对象的使用方法；3. 掌握check约束及规则的使用方法；4. 掌握触发器的概念、创建、修改和删除方法；5. 了解inserted和deleted的作用及数据的引用；6. 掌握各种触发器的工作过程。二、实验环境SQL Server 企业版三、实验学时2学时四、实验内容及步骤（一）创建学生课程数据库和表Create database stu_triGouse stu_trigocreate table s(sno char(5) not null,sname char(10),ss

2、ex char(2),sage int,sdept char(10),ssum_credit tinyint)gocreate table c(cno char(5) not null,cname char(10),ccredit tinyint)gocreate table sc(sno char(5) not null,cno char(5) not null,grade tinyint)go（二）主键约束-1.学生表-(1)主键的定义alter table s add constraint s_pk primary key(sno)go-（2）主键约束的违约检查和处理insert int

3、o s values('95001','李莹','女',21,'计科',0)insert into s values('95001','章张','男',22,'计科',0)-修改学号为，再次插入数据insert into s values('95002','章张','男',22,'计科',0)-（3）依次输入多个学生的记录insert into s values ('95003','

4、;陈华','女',22,'计科',0),('95004','吴华义','男',23,'计科',0),('95005','刘冠章','男',22,'计科',0),('95006','蒋婷','女',22,'网络',0),('95007','邱蔚六','男',21,'网络',0),('95008&#

5、39;,'王德柱','男',22,'网络',0),('95009','秦频','女',21,'网络',0),('95010','程少根','男',21,'网络',0)-2.课程表c-（1）主键的定义alter table c add constraint c_pk primary key(cno)-（2）主键的检查和处理（省）-（3）录入数据insert into cvalues('00001','

6、;计算机导论',2),('00002','高级语言',2),('00003','离算数学',3),('00004','数据结构',3),('00005','c#',2),('00006','面向对象',2),('00007','数据库原理',3),('00008','操作系统',3)-3.学生选课表-（1）主键的定义alter table sc add constr

7、aint sc_pk primary key(sno,cno)go-（2）主键约束的检查及处理insert into sc values('95001','00001',90)goinsert into sc values('95001','00001',86)Go-（3）录入数据insert into scvalues('95001','00002',86),('95001','00003',92),('95001','00004',

8、76),('95001','00005',86),('95002','00001',86),('95002','00002',80),('95002','00003',81),('95003','00001',88),('95003','00002',68),('95004','00001',96),('95005','00001',69)（

9、三）参照完整性-1.定义学生选课表的学号为外键alter table sc add constraint sc_fk foreign key(sno) references s(sno)goalter table sc add constraint sc_fk_1 foreign key(cno) references c(cno)go-2.参照完整性约束的检查-（1）向学生选课表中插入数据学号外键的检查insert into sc values('95100','00001',90)go-（2）向学生选课表中插入数据课程号外键的检查insert into sc

10、 values('95006','10000',90)go-（3）被参照表数据变化：删除学生表的某一个学生-1）删除学生选课表中的外键sc_fkalter table sc drop sc_fkgo-2)重新定义学生表的外键sc_fk及违约处理机制alter table sc add constraint sc_fk_sno foreign key(sno) references s(sno) on delete cascadeon update cascadego-3)查询学生选课表select * from sc-4) 删除学生表学号为95003的学生del

11、ete from s where sno='95003'go-5) 再次查询学生选课表select * from scgo-更新学生表中学号为的学生学号为-级联更新update s set sno=95105 where sno like '95005'go-查询学生选课表查看学生的学号是否改为select * from sc-(四) 用户定义完整性（省略非空约束）-1. 唯一性约束（学生姓名惟一）（1）约束的定义alter table s add constraint s_sname_unique unique(sname)（2）约束的检查和处理insert

12、into s values('96001','李莹','女',26,'信管',0)go2. 检查约束（1）约束的定义alter table s add constraint s_c1 check(sage>=12 and sage<=35)（2）约束的检查及处理insert into s values('96001','金林','男',9,'信管',0)go3. 默认约束-（1）约束的定义alter table s add constraint s_def

13、1 default '女' for ssex（2）约束的检查及处理insert into s(sno,sname,sdept,ssum_credit) values('96002','胡华林','信管',0)这时没有给性别赋值，但该学生的性别取了默认值“女”(五)触发器-1.insert触发器-（1）触发器的定义：-当向sc表中插入一个学生的成绩时，将s表中该学生的总学分加上添加的课程的学分。CREATE TRIGGER sc_tri_insON sc AFTER INSERTASBEGINDECLARE sno char(5),

14、 cno char(5)DECLARE xf tinyintSELECT sno=sno, cno=cno from insertedSELECT xf=ccredit FROM c WHERE cno=cnoUPDATE s SET ssum_credit= ssum_credit +xf WHERE sno=snoPRINT '修改成功'END（2） 触发器的验证insert into sc values('95006','00001',80)2）查询学生表select * from s从图中可以看出，学生表中学生李莹的学号改为“95001”

15、3）查询学生选课表select * from sc-2. delete触发器-（）触发器的定义-在删除s表中的一条学生记录时将sc表中该学生的相应记录也删除。CREATE TRIGGER s_delete_triON s AFTER DELETEASBEGINDELETE FROM sc WHERE sno IN(SELECT sno FROM deleted)END-1）查询学生表sselect * from s-2)查看“刘冠章”的选课记录,学号为95105select * from sc-3）在学生表中删除学号为“95105”的学生记录delete from s where sno li

16、ke '95105'-4) 查看sc表学号为“”的选课信息select * from sc where sno like '95105'-3.update触发器-（1）触发器的定义-创建触发器，当修改s表中的学号时，同时也要将sc表中的学号修改成相应的学号-（假设s表和sc表之间没有定义外键约束）CREATE TRIGGER s_update_triON s AFTER UPDATEASBEGINDECLARE old_num char(5), new_num char(5)SELECT old_num=sno FROM deletedSELECT new_nu

17、m=sno FROM insertedUPDATE sc SET sno=new_num WHERE sno=old_numEND-（2）触发器的验证1）UPDATE s SET sno='95100' WHERE sno='95001'2）查询学生表select * from s从图中可以看出，学生表中学生李莹的学号改为“95100”3）查询学生选课表select * from sc从图中可以看出，学生选课表中学生李莹的学号改为“95100”4. Instead of触发器在stu_tri数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视

18、图依赖于表s和sc，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表s和sc插入数据，从而实现向视图插入数据的功能。（1）首先创建视图：create view stu_viewasselect s.sno,sdept,cno,gradefrom s inner join sc on s.sno=sc.sno（2）向视图中插入数据Insert into stu_view values('98100','software','00001',90)这说明，该视图是不可更新视图，不能向该视图中直接插入数据。（3）创建视图CREATE TRIGGER InsteadTrigON stu_view INSTEAD OF INSERTASBEGINDECLARE XH char(5), XM char(10),ZY char(10), KCH char(5), CJ tinyintSET XM='佚名'SELECT XH=sno, ZY=sdept, KCH=cno, CJ=gradeFROM insertedINSERT INTO s(sno, sname, sdept) VALUES(XH, XM, ZY)INSERT INTO sc VALUES(XH, K