第5章：数据库完整性

目录

• 第5章：数据库完整性
  • 5.1、完整性概述
  • 5.2、实体完整性（primary key）
  • 5.3、参照完整性（foreign key）
  • 5.4、用户自定义的完整性
  • 5.5、触发器

第5章：数据库完整性

基于SQLServer学习使用，与MySQL有略微差别！

5.1、完整性概述

数据库的完整性是指数据的正确性和相容性

• 正确性：数据库中的数据要符合语义。
• 相容性：数据库中数据之间的关系要正确。

数据的完整性和安全性是两个不同概念

• 数据的完整性
  • 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
  • 防范对象：不合语义的、不正确的数据
• 数据的安全性
  • 保护数据库防止恶意的破坏和非法的存取
  • 防范对象：非法用户和非法操作

为维护数据库的完整性，DBMS必须：

1. 提供定义完整性约束条件的机制
2. 提供完整性检查的方法
3. 违约处理

5.2、实体完整性（primary key）

1、实体完整性规则

• 主码的值必须唯一；
• 主码的各个属性不能取空值；

2、实体完整性检查和违约处理

• 检查的内容

  • 检查主码的各个属性是否为空
  • 检查主码值是否唯一

• 对哪些操作进行实体完整性检查

  • 插入元组
  • 对主码列进行更新操作

• 违约处理方式

  • 拒绝

检查记录中主码值是否唯一的一种方法是进行全表扫描

为了提高效率，RDBMS会自动在主码上建立一个索引

5.3、参照完整性（foreign key）

1、参照完整性规则

• 若属性（或属性组）F是基本关系R的外码它与基本关系S的主码Ks相对应（基本关系R和S可能是相同的关系），则对于R中每个元组在F上的值必须为：
  • 或者取空值
  • 或者等于S中某个元组的主码值

2、参照完整性检查和违约处理

SC中的sno是外码，参照student中的sno

1. 在sc中插入元组
   违约处理：拒绝
2. 修改sc中的sno值
   违约处理：拒绝
3. 在student中删除元组
   违约处理：拒绝（默认）、级联删除
4. 修改student中的sno值。
   违约处理：拒绝（默认）、级联修改

3、级联更新、级联删除

-- 在定义外键的时候添加
on delete cascade -- 级联删除
on update cascade -- 级联更新

测试级联更新、级联删除：

-- 第一步：建表
create table category (
	id int,
	name varchar(20),
	primary key(id)
)

create table news (
	id int,
	content varchar(200),
	cid int,
	primary key(id),
	foreign key(cid) references category(id)
)
-- 第二步：插入数据
insert into category(id, name) values(1, '体育')
insert into category(id, name) values(2, '娱乐')
insert into category(id, name) values(3, '政治')

insert into news(id, content, cid) values(1, 'aaaaaaa', 1)
insert into news(id, content, cid) values(2, 'bbbbbbb', 1)
insert into news(id, content, cid) values(3, 'ccccccc', 2)
insert into news(id, content, cid) values(4, 'ddddddd', 2)
insert into news(id, content, cid) values(5, 'eeeeeee', 3)
insert into news(id, content, cid) values(6, 'fffffff', 3)
-- 第三步：测试增删改【都失败】
insert into news(id, content, cid) values(7, 'hhhhhh', 4)
update news set cid = 4 where id = 1
delete  from category where id = 1
update category set id = 4 where id = 2
-- 第四步：
-- 删除原有的默认情况下的外键
alter table news drop constraint FK__news__cid__3A81B327
-- 添加级联修改、级联删除的外键
alter table news add constraint FK__news__cid foreign key(cid) references category(id) 
	on delete cascade on update cascade
-- 第五步：再次测试第三步的增删改
select * from category
select * from news
insert into news(id, content, cid) values(7, 'hhhhhh', 4) -- no
update news set cid = 4 where id = 1     -- yes
delete  from category where id = 1   	   -- yes
update category set id = 4 where id = 2  -- yes

5.4、用户自定义的完整性

-- unique 约束 和 check 约束
-- 列级完整性约束
create table course1 (
	cno char(2) primary key,
	cname varchar(20) unique,
	cpno char(2) foreign key references course1(cno),
	ccredit smallint check(ccredit > 0)
)
drop table if exists course2
-- 表级完整性约束
create table course2(
	cno char(2),
	cpno char(2),
	ccredit smallint,
	cname varchar(20),
	primary key(cno),
	foreign key(cpno) references course2(cno),
	unique(cname),
	check(ccredit > 0)
)
-- cheeck约束
create   table  t1
	( 
		c1 int constraint PK_t1_c1 primary key,
		c2 int
	)
-- 增加约束：c1>0并且c2>4
alter table t1 add constraint CK_t1_c1c2 check(c1>0 and c2>4)

5.5、触发器

1、触发器概述

触发器是在对表或视图进行插入、更新或删除操作时自动执行的一段SQL语句

• 触发器定义在基本表或者视图上
• 自动触发执行，不能直接调用
  • 在表table1上定义了一个 insert 触发器 trigger1，
  • 当往表table1中插入元组的时候， trigger1自动执行

SQL Server中触发器的触发方式：

1. insert 触发
2. delete 触发
3. update 触发

SQL Server中定义触发器的语法：

create trigger trigger_name
on table_name
for / after / instead of [delete, insert, update]
as 
	SQL语句

for 和 after 的效果一样，执行完你选中的语句，再执行触发器中的语句；

instaed of 不会执行你选中的语句，而是执行触发器中的语句；

使用触发器：

create table table1
( c1 int )
select * from table1
-- 对table1表创建触发器（插入、删除时触发）
create trigger trigger1
on table1
after insert, delete
as
	select count(*) from table1
-- 插入一条数据
insert into table1 values(1)
-- 插入一条数据
insert into table1 values(2)
-- 插入两条数据
insert into table1
select 3
union
select 4
-- 删除
delete from table1

2、inserted、deleted 临时表

触发器触发时：

• 系统自动在内存中创建deleted表或inserted表
• 只读，不允许修改；触发器执行完成后，自动删除

inserted 表

• 临时保存了插入或更新后的记录行

deleted 表

• 临时保存了删除或更新前的记录行

inserted、deleted 临时表：

操作	instered 表	deleted 表
增加记录（insert）	存放新增加的记录	——
删除记录（delete）	——	存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

触发器对临时表的操作：

-- 第一步：建表测试
create table table2(
	name varchar(10), 
	grade int 
)
-- 第二步：创建触发器，在删除、修改、插入时执行
create trigger trigger2
on table2
for update, insert, delete
 as
	select * from inserted
	select * from deleted
-- 第三步：insert、delete、update
insert into table2 values('张三', 90 )
update table2 set grade = 45 where name='张三'
delete from table2 where name = '张三'
select * from table2
insert into table2 
 select '张三', 90 
 union
 select '李四', 40 
insert into table2 values ('李四', 12),('王五', 112)
update table2 set grade = 50

当交易记录表中有新交易时，账户表及时修改，保证数据的正确性

-- 账户表
create table count ( 
	id char(3) primary key,
	balance int
)
insert into count values('001', 1000)
insert into count values('002', 1000)
insert into count values('003', 1000)
-- 交易记录表
create table traninfo ( 
  id char(3) not null foreign key references count(id),
  amount int
)
-- 基于 traninfo 创建触发器
create trigger trigger3
on traninfo
after insert
as
	select * from inserted
	select * from deleted
	update count set balance = balance + inserted.amount from count
  inner join inserted on count.id = inserted.id
-- 添加数据
insert into traninfo values('001', 100)
insert into traninfo 
select '002', 200
union
select '003', -300
select * from count
select * from traninfo

触发器中 insted of（而不是） 的用法：

-- 如下代码：当对table2使用delete操作是，之后执行触发器中的as后的sql语句
create trigger trigger4
on table2 
instead of delete
as
	select count(*) as line_num from table2
-- 删除 table2 中的所有数据，实际不会执行这条语句，而是执行触发器中的语句
delete from table2

-- 使用触发器替代级联删除
create trigger trigger5
on count
instead of delete
as
	delete traninfo where id in ( select id from deleted)
	delete from count where id in ( select id from deleted)
-- 删除操作
delete from count where id = '001'