【数据库原理】学习笔记05——数据库完整性

数据库完整性

数据库的完整性：

是指数据的正确性和相容性。数据的完整性是为了防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据

完整性约束：

完整性约束条件也成为完整性规则，是数据库中的数据必须满足的语义约束条件。这些完整性一般由SQL的数据定义语句来实现，它们作为数据库模式的一部分存入数据字典中

完整性检查：

一般在INSERT、UPDATE、DELETE语句执行后开始检查，也可以在事务提交时检查。检查这些操作执行后数据库中的数据是否违背了完整性约束条件

违约处理：

如拒绝执行该操作或级联执行其它操作

实体完整性

在CREATE TABLE时用PRIMARY KEY定义

create table stu(
	sno varhar(10) primary key -- 列级定义
);

create table stu(
	sno varhar(10),
    primary key(sno) -- 表级定义
);

create table sc(
	sno varhar(10),
    cno varchar(10),
    primary key(sno, cno) -- 表级定义
);

完整性检查：

主码唯一；主码属性不为空

违约处理：

如果不满足约束则拒绝插入或修改

参照完整性

在CREATE TABLE时用FOREIGN KEY定义

FOREIGN KEY(键名) REFERENCES 主键表名(键名)

create table sc(
	sno varhar(10),
    cno varchar(10),
    primary key(sno, cno),
    foreign key(sno) references s(sno),
    foreign key(cno) references c(cno)
);

完整性检查：

sc表中插入/修改的数据必须在s、c表中能够对应找到；

从s/c表中删除/修改的数据必须在sc表中没有对应

删除被参照关系的元组时的三种策略:

删除/修改被参照关系的元组时的考虑

​ 级联删除/修改、受限删除/修改(拒绝执行)、置空值的删除/修改

在参照关系中插入元组时的考虑

​ 受限插入、递归插入

添加级联操作：

create table sc(
	sno varhar(10),
    cno varchar(10),
    primary key(sno, cno) on delete cascade
);

alter table sc 
add 
constraint d
foreign key(sno) references s(sno) on update cascade;

删除级联操作：

alter table sc drop foreign key d;

用户定义完整性

是针对某一具体应用的数据必须满足的语义要求

NOT NULL

UNIQUE

CHECK（条件）

DEFAULT (NOT NULL|SEX = ‘MALE’…)

…

属性上的约束（检查自己）|元组上（检查别人和自己）

完整性检查和违约处理： 不满足则拒绝执行

在sql语句中定义：

CONSTRAINT <完整性约束条件名> <完整性约束条件>

create table s(
	sno varchar(10),
    sname varchar(10),
    constraint c1 not null,
    constraint c2 check(sname != 'name')
);

断言&触发器

断言

描述一般性约束，断言不为真的操作都会被拒绝执行

创建： CREATE ASSERTION <断言名> <CHECK 子句>

删除： DROP ASSERTION <断言名>

断言创建后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言返回值不为真的操作都会拒绝执行。check返回值为true/false，如果为true则执行操作，false则拒绝执行

create assertion sc_count
check(60 >= all(select count(*) from sc group by cno));

触发器

事件-条件-动作

任何用户对表进行的增、删、改操作都会由服务器自动激活相应的触发器

创建：

​ CREATE TRIGGER <触发器名>

​ {BEFORE|AFTER} <触发事件> ON <表名> – before：先执行触发操作再执行增删改|after：相反；不能定义在视图上
​ REFERENCING NEW|OLD ROW AS <变量>
​ FOR EACH {ROW|STATEMENT} – 行级：语句执行多少行就触发几次|语句级：几条语句触发几次
​ [WHEN <触发条件>] <触发动作体>

删除：

​ DROP TRIGGER<触发器名>ON<表名>

create trigger ins_s_sc after insert on s
for each row
insert into sc(sno, cno) values(new.sno, '001');

例：定义一个before行级触发器，为教师表Teacher定义完整性规则“教师的工资不得低于4000元，如果低于4000元，自动改为4000元

create trigger insert_or_update_sal
before insert or update on Teacher
referencing new row as newtuple
for each row 
begin
	if(newtuple.job='教授') and (newtuple.sal<4000)
	then newtuple.sal:=4000
	end if;
end;