数据库 第五章 数据库完整性

理论知识

完整性定义：数据的正确性和相容性

为了维护数据库完整性，数据库管理系统必须做到

1.提供定义完整性约束条件的机制

2.提供完整性检查的方法

3.违约处理

完整性分类:

1.实体完整性

2.参照完整性

3.用户定义完整性

一，实体完整性：

定义：primary key

分类：列级约束条件 和 表级约束条件

[多属性构成的主码只能使用表级约束]

检查情况：

insert，有无Null,主码有无重复

update，是否对主码操作

[插入 或 对主码进行更新时]

违约处理：拒绝（no action）

二，参照完整性：

定义：foreign key x(属性) references x(属性)

分类：

列级约束条件：references x

表级约束条件：foreign key x references x 

检查情况：

对于被参照表（父表）：

update 主码 或 delete

（违约处理：拒绝no action或级联cascade）

对于参照表（子表）：

update 外码 或 insert 

（违约处理：拒绝）

三，用户定义完整性

约束分类：

唯一性 union

非空性 Null 或 not null

满足条件 check

默认值 default

约束条件名：constraint

触发器分类：delete触发器,update触发器,insert触发器

系统表：inserted表 和 deleted表

（没有updated表，因为update操作=先delete操作，后insert操作）

系统表知识点：

1.系统表自动创建（触发器被激活时）

2.只能读两个系统表，且不可修改

3.触发器执行后，被自动删除

4.表结构与触发器作用的表结构相同（同时改变）

创建，修改，删除触发器：

create,alter,drop trigger x（触发器名）

 

代码部分

5.1

实体完整性

creat table s(

sno char(9)

sname varchar (5)

primary key (sno)       [表级约束]

)

 

creat table c(

cno char (2)

cname varchar(50)

)

 

alter table c constraint pk_c

primary key (cno)     [追加约束]

 

 

5.2

creat table sc1(

sno char(8),

cno char(5),

grade int,

semester char(6),

teachingclass char(8),

primary key (sno,cno)        [表级约束]

)

 

foreign key (sno) references student (sno)

on delete no action 

on update no action

foreign key (cno)references course (cno)

on delete cascade

on update cascade

 

sikao5.2

create table 系部1(

xibianhao char (2) primary key

ximingchen varchar (20)

xizhuren varchar(20)

 

) 

 

select * form xibu 1

create table xuesheng1(

xuehao char(5) primary key

xingbie char(2)

suozaixibianhao char (2)

foreign key (suozaixibianhao)references xibu1(xibianhao)

on update cascade

on delete no action

)

 

 

5.3

creat table 部门(

int primary key ,--indentity(1,1),

vachar(20)unique not null,

char(20) default('行知楼')

int check (人数>=0 and 人数<=50)

)

 

creat trigger trig 1

on stdent for insert,delete,update

as

begin

select * form inserted

select * form deleted

end

select * form student

 

5.5

create trigger trig2

on stduent for delete

as

declare @msg varchar(50)

select @msg = str (@@ row count)+'名学生记录被删除'

select @msg 

return

drop trigger trig 2

 

5.6

create trigger trig 3

on sc for insert

as

if not exists(select * from student 

where sno = (select sno from inserted))

or not exists (select * from course

where cno=(select * from inserted ))

begin 

print'数据不一致，xxx'

rollback transaction 

end

 

drop trigger trig 3

 

5.7

create trigger trig 4

on student for update 

as

if update (sno)

begin

print'不允许修改学号'

rollback transaction

end

drop trigger trig 4

 

 5.8

1.

create trigger trig 5

on sc for insert

as

begin

declare @xh char (9),@kh char (3)

if(select grade from inserted)<60

begin 

set @xh =(select sno from inserted)

set @kh =(select cno from inserted)

update sc grade = 60 where sno =@xh and cno = @kh 

end 

 

 

drop trigger trig 5

 

2.

create trigger trig 5

on sc instead of insert

as

begin

declare @xh char(9),@kh char(3)

if(select grade from inserted)<60

begin

set @xh=(select sno from inserted )

set @kh=(select cno from inserted )

insert into sc values (@xh,@kh,60)

 

end

else

insert into sc select * inserted

drop trigger trig 5

 

思考题5-3】当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

-- SC_U（Sno,Cno,Oldgrade,Newgrade）

--创建sc_u表

create table sc_u( sno char(9) , cno char(3) , Oldgrade int , Newgrade int )

--select * from sc--select * from sc_u

--创建触发器

create trigger trig_update_scon scafter updateasbegin

if object_id(N'sc_u',N'U') is null

--判断sc_u表是否存在

create table sc_u(sno char(9),cno char(3),Oldgrade int,Newgrade int)--创建sc_u表 declare @oldscore int, @newscore int,@xh char(9),@kh char(3); select @xh=sno,@kh=cno,@oldscore=grade from deleted; select @newscore=grade from inserted; --显示两张表的数据 select * from inserted select * from deleted --向sc_u表插入一条记录 if @newscore>=@oldscore*1.1 insert into sc_u values(@xh,@kh,@oldscore,@newscore) end

测试触发器--因为是后触发器，所以先更新数据后，才触发触发器;update sc set grade=65 where sno='20180003' and cno='81001'update sc set grade=99 where sno='20180003' and cno='81002'--删除触发器drop trigger trig_update_sc--??? 多条记录更新该如何完成呢？--游标alter trigger trig_update_scon scafter updateasbegin if object_id(N'sc_u',N'U') is null--判断sc_u表是否存在 create table sc_u(sno char(9),cno char(3),Oldgrade int,Newgrade int)--创建sc_u表 --显示两张表的数据 select * from inserted select * from deleted select i.sno ,i.cno ,d.grade ,i.grade from inserted i join deleted d on i.sno=d.sno and i.cno=d.cno /*使用游标逐条比较两张表中的记录*/ --定义变量 declare @oldscore int, @newscore int,@xh char(9),@kh char(3); --定义游标 declare sc_cursor cursor for select i.sno ,i.cno ,d.grade ,i.grade from inserted i join deleted d on i.sno=d.sno and i.cno=d.cno --打开游标 open sc_cursor --读取第一条数据 fetch next from sc_cursor into @xh,@kh,@oldscore,@newscore while @@fetch_status =0 --检测是否还有数据读取 begin if @newscore>=@oldscore*1.1 insert into sc_u values(@xh,@kh,@oldscore,@newscore) fetch next from sc_cursor into @xh,@kh,@oldscore,@newscore --读取下一条数据 end close sc_cursor --关闭游标 deallocate sc_cursor --释放游标enddelete from sc_uselect * from scselect * from sc_uupdate sc set grade=grade*1.2 where sno='20180003' and cno='81001'update sc set grade=grade*1.2 where sno='20180003' --

 

【思考5-4】将每次对表Student的插入操作所增加的学生个数 --记录到表StudentInsertLog中。create trigger trig_insert_stu_counton studentafter insertasbegin if object_id(N'StudentInsertLog',N'U') is null--判断StudentInsertLog表是否存在 create table StudentInsertLog( insert_user varchar(50), insert_datetime datetime, insert_stu_Count int default(0));--创建StudentInsertLog表 declare @ct int; select @ct = count(*)from inserted; insert into StudentInsertLog values(current_user,getdate(),@ct); endselect * from studentselect * from StudentInsertLog--1条记录insert into student(sno,sname) values('20001','h')--多条create table t1(sno int, sname varchar(50))declare @bh int,@xm varchar(50)set @bh=1set @xm ='xm'+str(@bh)while (@bh<10)begin set @bh=@bh+1 set @xm ='xm'+str(@bh) insert into t1(sno,sname) values(@bh,@xm)endselect * from t1insert into student(sno,sname) select sno,sname from t1 --

 

【思考5-5】定义一个触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元， --如果低于4000元，自动改为4000元”。create table teacher( tid int , tname varchar(50), salary int, rank varchar(10))CREATE TRIGGER IUSal ON Teacher FOR INSERT,UPDATE AS IF exists( SELECT * FROM INSERTED where rank='教授' and salary<4000) UPDATE teacher SET Salary =4000 WHERE rank='教授' and salary<4000 select * from teacherinsert into teacher values(1,'T1',2000,'讲师') insert into teacher values(2,'T2',2000,'教授') select * from teacherinsert into teacher values(3,'T3',5000,'教授') insert into teacher values(4,'T4',2000,'讲师') insert into teacher values(5,'T5',3000,'讲师') insert into teacher values(6,'T6',2000,'教授') update teacher set salary=2000 where tid<>2select * from teacher