数据库第五讲 完整性

本文主要是介绍数据库第五讲 完整性，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

第五讲 完整性

• • 完整性概述
  • 实体完整性
  • 参照完整性
  • 用户自定义的完整性
  • 触发器

完整性概述

数据库的完整性是指数据库中数据的正确性 、一致性和相容性

• 数据的正确性保证数据库的数据是符合语义约束的数据
• 一致性保证数据之间的逻辑关系是正确的，对数据库更新时，数据库从一个一致状态到另一个一致状态
• 相容性则要求同一个事实的两个数据应当是一致的
  数据库中的数据要成为有意义的信息，必须满足一定的语义约束条件

约束的分类
就被约束的数据对象而言，完整性约束又可以分为如下表所示的四种类型

从约束的状态的角度，约束还可以分静态约束和动态约束

• 静态约束时关于数据库正确状态的约束
• 动态约束时数据库从一种正确状态转移到另一种状态的转移约束

DBMS对约束的支持

//1.提供说明和定义完整性约束的方法
	CTEATE TABLE Teachers
	(Tno	CHAR(7)PRIMARY KEY,
	Tname	CHAR(10),
	Sex		CHAR(2)CHECK(Sex='男'OR Sex = '女')，
	Birthday	DATE.
	Title	CHAR(6),
	Dno		CHAR(4),
	FOREIGN KEY (Dno) REFERENCES Departments(Dno));
//这条create语句定义了Teachers表 PRIMARY KEY（主码约束）、CHECK、FOREIGN KEY（外码约束）、REFERENCES 都是约束的定义

//2.提供完整性检查机制
//约束的定义都存放在数据字典里，每当用户更新数据时，DBMS都会进行完整性检查
//3.违约处理
//当更新违反了完整性约束时，DBMS会采取相应的措施，保证数据的完整性

实体完整性

实体完整性约束的定义

//创建选课表SC用如下语句
CREATE TABLE Sc
(Sno	CHAR(9),
Cno		CHAR(5),
Grade	SMALLINT CHECK(Grade>=0 ANDGrade<=100),
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno)REFERENCES Students(Sno),
FOREIGN KEY(Cno)REFERENCES Courses (Cno));

实体完整性规则

• 关系R的所有元组在主码上的值必须唯一
• 构成主码的热和属性都不能取空值

违反实体完整性规则的操作

//插入新元组时可以破坏实体完整性规则
INSERT INTO Students
VALUES('201816010','司马义','男',1999-01-28,'2018','计算数学','MATH')
//学号重复
//修改元组的主码时可以破坏实体完整性规则

违约处理
DBMS应该自动检查是否导致违反实体完整性约束，并拒绝导致破坏实体完整性约束的任何插入或修改

参照完整性

参照完整性约束的定义

//创建选课表SC用如下语句
CREATE TABLE Sc
(Sno	CHAR(9),
Cno		CHAR(5),
Grade	SMALLINT CHECK(Grade>=0 ANDGrade<=100),
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno)REFERENCES Students(Sno),
FOREIGN KEY(Cno)REFERENCES Courses (Cno));

外码与ER模型
参照完整性是对外码取值的约束，外码的来源有一下几种情况

• 一对一联系
• 一对多联系
• 多对多联系
• 弱实体集

下列关系模式中存在的外码
Departments(Dno , Dname, Dheadno)
Teachers(Tno , Tname,Sex, Birthday, Title, Dno)
Students(Sno , Sname,Sex, Birthday, Enrollyear,Speciality, Dno)
Courses(Cno ,Cname, Period, Credit)
Teaches (Tno,Cno )
sC(Sno,Cno ,Grade)
Evalues (Sno,Tno,Cno , Escore)

家属（职工号，家属姓名，性别，出生年月，与职工关系)

参照完整性规则
参照关系R的任何元组在其外码FKr上的值或者等于被参照关系S的某个元组在主码Ks上的值，或者为空值

违反参照完整性的更新

• 像参照关系R中插入元组
• 修改参照关系R外码上的值
• 删除被参照关系S的元组
• 修改被参照关系S主码上的值

//例子
INSERT INTO SC(Sno,Cno)
VALUES('201816010','MA302')
//若Students中没有201816010这个学生，违反参照

DELETE FROM Students
WHERE Sno='201824010';
//从Students中删除201816010这个学生后，SC表中的这个学生的选课记录就没有可参照的对象，因此违反


UPDATE Students
SET Dno = 'CS'
WHERE Sno = '201824010'
//如果Department表中没有CS这个院系，则违反

违约处理

• 向参照关系R中插入元组：拒绝
• 修改参照关系R外码上的值：拒绝
• 删除被参照关系S的元组：拒绝/级联删除/置空值/置缺省值
• 修改被参照关系S主码上的值：拒绝/级联删除/置空值/置缺省值

违约定义
定义外码时可以通过参照触发动作定义违约时出发的动作
FOREIGN KEY (A1,...,Ak)REFERENCES<外表名>(<外表主码>)

• [<参照触发动作>]
• <参照触发动作>指出修改和删除违反参照完整性约束时触发的动作；缺省时，违反参照完整性的修改和删除将被拒绝
• <参照触发动作>可以是如下两种形式：1.ON UPDATE <参照动作>[ON DELETE<参照动作>]2.ON DELETE <参照动作>[ON UPDATE <参照动作>]
• 其中<参照动作>可以是CASCADE 、SET NULL、SET DEFAULT和NO ACTION之一，分别表示级联、置空值、置缺省值和拒绝

//例子：如果我们希望在修改Students元组的主码时，同时修改SC表中相应元组的外码，
//删除Students的元组时，同时删除相应的SC元组。
//而修改Courses的元组时，同时修改相应的SC元组的外码，但不允许删除Courses的元组，破坏参照完整性

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(5),
Grade SMALLINT CHECK (Grade>=O AND Grade<=100),
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno)REFERENCES Students (Sno)
	ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Cno)REFERENCES Courses (Cno)
	ON UPDATE CASCADE );

用户自定义的完整性

用户自定义的完整性约束：用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求，用户定义的完整性可以是∶属性级、关系级、数据库级

属性级约束
属性级的用户自定义约束指的是定义在列级上的CHECK约束、NULL约束、UNIQUE约束

CREATE TABLE Students
(Sno	CHAR(9) PRIMARY KEY,
Sname	CHAR(10)NOT NULL,
Sex	CHAR(2)CHECK(Sex='男'OR Sex='女'),
Birthday	DATE，
Enrollyear CHAR(4),
Speciality	CHAR(20),
Dno	CHAR (4),
FOREIGN KEY (Dno)REFERENCES Department (Dno));

关系级约束
关系级约束说明关系的合法取值，常常涉及同一关系的多个属性和/或多个元组

//EmpSalary (职工工资)表具有如下模式∶
EmpSalary(Eno,Ename, Dno, BaseSalary, Subsidy, Bonus,Tax, Insurance,PayLoan)
CREATE TABLE EmpSalary
(Eno	CHAR (7)PRIMARY KEY,
Ename	CHAR(8)NOT NULL,
Dno		CHAR(4),
BaseSalary	NUMERIC (7,2),
Subsidy		NUMERIC (7,2),
Bonus	NUMERIC (7,2),
Tax		NUMERIC (7,2),
Insurance NUMERIC (7,2),
PayLoan NUMERIC (7,2)DEFAULT 0.00,
CHECK(((BaseSalary+Subsidy+Bonus)-(Tax+Insurance+PayLoan))>=0.5* BaseSalary)
FOREIGN KEY(Dno)REFERENCES Departments (Dno));

断言与数据库级约束

• 断言(Assertion)是一种命名约束，它表达了数据库状态必须满足的逻辑条件
• 断言被用来表达数据库约束，这些约束不能或很难用其他方法表达
• SQL创建断言的语句具有如下格式︰

CREATE ASSERTION<断言名>
CHECK(<条件>)[<约束性质>]

例子：设数据库包含如下关系模式:Employees (Eno, Ename, Salary,Dno)，Departments (Dno, Dptname, Mrgno)
约束“任何部门经理的工资不超过其所在部门平均工资的10倍”涉及上述两个关系，可以为它创建一个断言，该约束等价于“不存在一个部门经理，其工资高于他所在部门平均工资的10倍。”可以用SQL创建一个断言SalaryConstraint来表达这一约束:

CREATE ASSERTION SalaryConstraint CHECK
(NOT EXISTS
(SELECT*
FROM Departments D, Employees E
WHERE D.Mrgno=E.Eno AND
Salary>10*(SELECT AVG (Salary)
FROM Employees
WHERE Dno=D.Dno)));

例子：

• 断言创建后，系统会检测其有效性。如果断言有效，则以后只有不违反断言的数据库更新才被允许
• 如果断言比较复杂，则检测的开销相当高，从而降低了数据更新操作的性能。因此，实际系统必须谨慎地使用断言
• 删除断言
  因为没有其他数据库对象的定义依赖断言，因此可以用如下形式的语句直接删除断言︰DROP ASSERTION<断言名>

触发器

问题引入：
虽然DBMS提供了多种完整性约束定义和检查机制。然而，除了违反参照完整性约束的删除和修改进行级联删除和修改外，违反其他约束的更新都被简单地拒绝
两个例子∶

• 银行的透支处理:银行允许用户透支，但存款余额不能取负值。透支时不是简单地拒绝更新，而是触发一系列动作完成上述任务，同时避免破坏完整性约束
• 存货预警:当库存量下降到最小库存量以下时，自动提示仓库管理人员订货或自动产生一个订单

触发器(trigger) 是特殊类型的存储过程，当某个事件发生时它自动执行
要设置触发器机制，必须满足两个要求：

• 1.指明什么事件发生和满足什么条件执行的触发器；
• 2.指明触发器执行什么样的动作
• 这种模型称作事件-条件-动作模型
• 数据库系统将像保存数据一样存储触发器。只要指定的事件发生，触发条件满足，相应的存储过程就被执行

SQL中的触发器
在SQL-92标准中没有触发器，但是支持SQL的DBMS广泛支持触发器。然而，不同系统的触发器并不兼容
下面介绍基于sQL-99中的触发器，IBM DB2和Oracle中的触发器与此类似
创建触发器语句的一般格式如下:

CREATE TRIGGER<触发器名>触发时间><触发事件>ON<表名>
[REFERENCING<旧/新值别名>,...,<旧/新值别名>]
[FOR EACH {ROW | STATEMENT}]
[WHEN (<触发条件>)]<被触发的SQL语句>
/*
<触发时间>可以是BEFORE:事件前，AFTER:事件后

<触发事件>可以是T上的INSERT、DELETE、UPDATE或UPDATE OF<触发列>,...<触发列>

REFERENCING子句创建一些过渡变量用来存放表T和表T的行更新前的l旧值和更新后的新值。

<旧/新值别名>可以是如下形式之一
	OLD/NEW[RoW][AS]<变量>︰创建行过渡变量<变量>存放表T的行更新前/后的值
	OLD/NEW TABLE [AS]<变量>∶创建表过渡变量<变量>存放表T更新前/后的值
	
FOR EACH ROW定义行级触发器，而FOR EACH STATEMENT定义语句级触发器缺省时为语句级触发器

WHEN子句说明触发条件，缺省时无条件触发。<触发条件>是一个任意布尔表达式

<被触发的SQL语句>是触发动作体，具有如下形式:
	BEGIN ATOMIC
		<可执行的SQL语句>;
	END

*/

例子：
设银行数据库包含如下关系模式︰

• Branch(BranchName, BranchCity, Assets)
• Customer (CustomerName, CustomerStreet, CustomerCity)
• Account (AccountNumber, BranchName, Balance)
• Loan (Loan-number, BranchName,Amount)
• Depositor(CustomerName,AccountNumber)
• Borrower (CustomerName, Loan-Number)

某元组t的Balance修改后的值小于零时，系统做如下三件事:

• 向贷款关系Loan插入一个新元组t，其中t1.LoanNumber=t.AcountNumber , t1.BranchName = t.BranchName,t1.Balance等于透支额
• 向Borrower插入新元组t2，记录新的贷款与顾客之间的联系。假设记录存款关系Account元组t与顾客之间的联系的Depositor元组为t3，则t2的两个属性都取自t3
• 将Account的元组t的Balance置零

CREATE TRIGGER OverdraftTrigger 
AFTER UPDATE OF Balance ON Account
REFERENCING NEW ROW AS t
FOR EACH ROW
WHEN (t.Balance < 0)
BEGIN ATOMIC
	INSERT INTO Loan
	VALUES (t.AccountNumber, t.BranchName, -t.Balance);
	INSERT NTO Borrower
	(SELECT CustomerName, AccountNumber
	FROM Depositor
	WHERE t.AccountNumber = Depositor.AccountNumber);
	UPDATE Account SET Balance= 0
	WHERE Account.AccountNumber = t.AccountNumber
END

删除触发器
当触发器没用时可以删除它，语法如下：DROP TRIGGER <触发器名>

这篇关于数据库第五讲 完整性的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！