数据库11-断言与参照完整性

完整性约束

控制机制

完整性约束是加在数据库模式上的一个具体条件，规定什么样的数据能够存储到数据库中；定义机制：

约束条件：数据模型的组成部分，约束数据库的寓意
DBMS提供定义数据库完整性约束条件，并作为模式的一部分存入数据库

检查机制：

检查用户发出的操作是否违背了完整性约束条件

违约反应：

发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作保证数据完整性；

分类

主键约束PRIMARY KEY ：一个关系仅一个，属性非空
唯一约束UNIQUE：一个关系可声明多个，可为空，允许多个空值，不能定义在被主键约束的属性上
非空约束NOT NULL
自定义约束CHECK
参照完整性约束FOREIGN KEY
断言ASSERTION

还可以根据位置分类为列级，行级和表级约束；以下是一些表建立的约束的例子

CREATE TABLE Diagnosis{
DGno VARCHAR(10) PRIMARY KEY,
Pno VARCHAR(10) NOT NULL,
Dno VARCHAR(10) NOT NULL,
Symptom VARCHAR(100),
Diagnosis VARCHAR(100),
DGtime DATETIME,
Rfee DECIMAL(18,2) NOT NULL
};
CREATE TABLE Doctor{
Dno VARCHAR(10),
Dname VARCHAR(50) NOT NULL,
Dsex VARCHAR(2) CHECK( Dsex IN (‘男’, ‘女’)),
Dage INT CHECK( Dage > 0 AND Dage <60),
Ddeptno VARCHAR(10),
Dlevel VARCHAR(50),
Dsalary DECIMAL(18,2),
PRIMARY KEY(Dno),
};
CREATE TABLE RecipeDetail{
Rno VARCHAR(10),
Mno VARCHAR(10) NOT NULL,
Mamount DECIMAL(18,0),
PRIMARY KEY(Rno,Mno),
CHECK (Mno IN (SELECT Mno FROM Medicine))//这是参照完整性约束，允许出现其他关系的子查询
};

以下是断言的格式和一个例子

CREATE ASSERTION fk_Diagnosis
CHECK 
( NOT EXISTS
( SELECT * 
FROM Diagnosis 
WHERE Dno NOT IN
(SELECT Dno
FROM Doctor))); 


CREATE ASSERTION <断言名> CHECK<谓词>
Create assertion salarycheck check(
Not exists(
Select * from Doctor x
Where Dsalary >= some ( select Dsalary from Doctor y 
Where x.Deptno=y.Deptno and y.Dno =(
Select Manager from Dept 
Where x.Deptno =Dept.Deptno)
)
);

参照完整性

概念

给定关系R，S，若存在R.A参照S.B

R是参照表，S为被参照表
A取值要么为空，要么来自于S.B
A为外码
B是S的主码若两张表的属性有参照完整性约束，在创建R表声明

REDERENCES S(B)
FOREIGN KEY(A) REFERENCES S(B)

可见对R和S的删除，更新，插入都有可能触发完整性违约；

DELETE FROM S
UPDETE S.B
UPDETE R.A
INSERT TO R

策略

受限策略RESTRICTED:系统的默认方式，出现违约时，系统拒绝执行
置空策略SET-NULL：根据环境的语义，外码可能为空
级联策略CASCADE：不用拒绝用户操作请求的处理方式
定义方式ON (DELETE|UPDATE) (SET NULL|CASCADE)