数据库—完整性
完整性
- 数据库的完整性是指数据库中数据是正确的、一致的和相容的
- 数据的正确性保证数据库的数据是符合语义约束的数据
- 一致性保证数据之间的逻辑关系是正确的，对数据库更新时，数据库从一个一致状态到另一个一致状态
- 相容性则要求同一个事实的两个数据应当是一致的
- 数据库中的数据要成为有意义的信息，必须满足一定的语义约束条件
约束的分类
①就被约束的数据对象而言，完整性约束又可以分为如下表所示的四种类型
| 约束类型 | 含义 | |
|---|---|---|
| 类型/域约束 | 说明给定类型的合法取值 | 用户自定义的完整性 |
| 属性约束 | 说明属性的合法取值 | 用户自定义的完整性 |
| 关系约束 | 说明关系的合法取值 | 实体完整性/用户自定义的完整性 |
| 数据库约束 | 说明数据库的合法取值，通常涉及多个关系 | 实体完整性/用户自定义的完整性 |
②从约束的状态的角度，约束还可以分静态约束和动态约束
- 静态约束是关于数据库正确状态的约束
- 动态约束是数据库从一种正确状态转移到另一种状态的转移约束
实体完整性
①实体完整性规则：
- 每个关系应该有一个主码，每个元组的主码值惟一确定该元组
- 主码的任何属性都不能取空值
②违反实体完整性规则的操作
- 插入新元组时可能破坏实体完整性规则
- 修改元组的主码时可能破坏实体完整性规则
- DBMS应该自动检查是否导致违反实体完整性约束，并拒绝导致破坏实体完整性约束的任何插入或修改
- SQL支持实体完整性。用户只需要在创建基本表时说明关系的主码，系统就能够自动地保证实体完整性
参照完整性
①参照完整性规则：
- 参照关系R的任何元组在其外码FKR上的值或者等于被参照关系S的某个元组在主码Ks上的值，或者为空值
②违反参照完整性的更新
- 向参照关系R中插入元组
- 修改参照关系R外码上的值
- 删除被参照关系S的元组
- 修改被参照关系S主码上的值
③违约处理
- 向参照关系R插入元组：拒绝
- 修改参照关系R外码上的值：拒绝
- 删除被参照关系S的元组：拒绝/级联删除/置空值/置缺省值
- 修改被参照关系S主码上的值：拒绝/级联删除/置空值/置缺省值
④SQL中的参照完整性
- 外码可以在创建基本表时用FOREIGN KEY子句说明, 形式为：
- FOREIGN KEY (A1,…, Ak) REFERENCES <外表名> (<外表主码>) [<参照触发动作>]
- <参照触发动作>指出修改和删除违反参照完整性约束时触发的动作；缺省时，违反参照完整性的修改和删除将被拒绝
- <参照触发动作>可以是如下两种形式之一：
ON UPDATE <参照动作> [ON DELETE <参照动作>] ON
DELETE <参照动作> [ON UPDATE <参照动作>] 其中< - 参照动作>可以是CASCADE、SET NULL、SET DEFAULT和NO ACTION 之一，分别表示级联、置空值、置缺省值和拒绝
- ON DELETE <参照动作>缺省时，违反参照完整性的删除将被拒绝
- ON UPDATE<参照动作>缺省时，违反参照完整性的修改将被拒绝
eg：
如果我们希望在更新Students元组的主码时同时修改相应的SC元组的外码Sno，删除Students的元组时同时删除相应的SC元组；而更新Courses的元组时同时修改相应的SC元组的外码Cno，但不允许删除Courses的元组破坏参照完整性，则我们可以用如下语句创建基本表SC：
CREATE TABLE SC
(Sno CHAR (9),
Cno CHAR (5),
Grade SMALLINT CHECK (Grade>=0 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 );用户自定义的完整性约束
- 用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求，用户定义的完整性可以是：属性级，关系级，数据库级
①域约束
- 域完整性约束是最简单、最基本的约束
- 域完整性约束：每个属性都必须在一个值域上取值
- 一个属性能否取空值由其语义决定
- 域约束使得新的值插入到数据库中时，系统可根据约束对新插入的值进行完整性检查
- 域约束的恰当定义还可以对查询进行检测，从而保证比较是有意义的
- 域约束在原理上非常类似于编程语言中变量的类型，就像不同的变量可以有相同的数据类型一样，不同的属性可以有相同的域
DBMS提供了一些标准数据类型(域), 用户可以使用它们说明属性类型
域定义允许用户定义新的域，声明一个域包括
- (1) 域值类型：包括数据的类型、长度、单位、精度等
- 例如，可以规定PersonName（人名）域的数据类型是字符型，长度为8；RMB（人民币）域和Dollars（美元）域的类型都是长度为12位十进制数，小数点后有两位
- (2) 缺省值。例如可以规定RMB和Dollars域的缺省值为0.00
- (3) 域值的格式。例如可以规定出生日期的格式为：YYYY.MM.DD
- (4) 对取值范围或取值集合的约束
- 例如，可以规定性别域的取值集合为{男，女}，学生成绩域的取值范围为[0,100]
- 在一个域上可以定义多个约束条件。域值的格式可以作为约束条件定义
用户可以使用定义的域说明属性。这样做可以防止无意义的比较
- 例如，如果用字符串类型说明属性Dname（部门名）和Ename（职工名），则比较Dname=Ename是合法的，尽管字符串长度可能不同。但是，这种比较没有实际意义
- 如果Dname说明为部门域，Ename说明为人名域，则系统可以通过域约束检查，发现比较Dname=Ename不合法
不同域上的值不能比较
- 例如，两个分别取值于RMB和Dollars的量不能比较，尽管它们都是长度为12位十进制数，小数点后有两位
- DBMS允许将一个域中的值转换到另一个域中。例如，设属性r.A在RMB域上取值，SQL允许我们使用如下方法将它转换到Dollars域：
CAST (r.A/7.54 AS Dollars) (假设7.54元人民币兑换1美元）
SQL对域约束的支持
SQL支持域约束，允许用户创建新的域、定义域约束，修改和删除已经定义的域
- 创建域：SQL用如下形式的语句创建一个域
- CREATE DOMAIN <域名> [AS ] <数据类型>
- [DEFAULT <缺省值>] [<域约束>, …, <域约束> ]
- 该语句创建一个名为<域名>的域，它的值类型由AS <数据类型>说明
<数据类型>可以使用SQL的任何内置类型，DEFAULT子句定义缺省值，<缺省值>是域中的一个合法值（满足域约束）
域定义中可以包含零个或多个<域约束>来约束域值的取值。<域约束>具有如下形式：
- [CONSTRAINT <约束名>] CHECK (<条件>) [<约束性质>]
- 其中可选短语“CONSTRAINT <约束名>”为约束命名
- <条件>的常见形式是涉及域值的布尔表达式，
- 其中域值用VALUE表示 <约束性质>可以是
- NOT DEFERRABLE（不可延迟的）
- DEFERRABLE（可延迟的）
- 缺省时为不可延迟
eg：
定义Dollars域的类型是长度为12位十进制数，小数点后有两位，其缺省值为0.00
CREATE DOMAIN Dollars NUMERIC (12,2) DEFAULT 0.00;定义HourlyWage域是6位十进制数，小数点后有两位
且命名约束WageValueTest确保其值大于或等于最低工资5.00。
CREATE DOMAIN HourlyWage NUMERIC(6,2)
CONSTRAINT WageValueTest CHECK (VALUE>=5.00);
也可以用CHECK子句限制所定义的域不能取空值。例如
CREATE DOMAIN PersonName CHAR (8)
CONSTRAINT PersonNameNullTest
CHECK (VALUE NOT NULLL);使用IN可以将域限制为只包含指定的一组值。例如
CREATE DOMAIN SexType CHAR (2)
CONSTRAINT SexTest CHECK (VALUE IN (‘男’, ‘女’));修改域
SQL允许修改域约束（设置缺省值、删除缺省值、添加约束和删除约束），格式如下
- ALTER DOMAIN <域名> <修改动作>
- 其中<修改动作>可以是：
- 设置缺省值 SET DEFAULT <缺省值>
- 删除缺省值 DROP DEFAULT
- 添加域约束 ADD <域约束> (其中<域约束>与CREATE DOMAIN相同)
- 删除<约束名>命名的域约束 DROP CONSTRAINT <约束名>
eg:
下面的语句将为HourlyWage域设置缺省值5.00：
ALTER DOMAIN HourlyWage SET DEFAULT 5.00;下面的两个语句将SexType域上的约束SexTest修改为在{‘M’, ‘F’}上取值：
ALTER DOMAIN SexType DROP CONSTRAINT SexTest;
ALTER DOMAIN SexType
ADD CONSTRAINT SexTest CHECK (VALUE IN (‘M’, ‘F’));删除域
- 当不需要某个域约束时，可以使用DROP DOMAIN语句将它删除。
- 语句格式为： DROP DOMAIN <域名> {CASCADE | RESTRICT}
- 其中CASCADE表示级联删除，RESTRICT表示受限删除
- 声明RESTRICT时，如果存在基于该域定义的列，则不能删除
- 声明CASCADE时，与删除模式和删除基本表不同，域删除后并不删除依赖于该域定义的列，而是将列定义（包括类型、缺省值、约束）用定义域的标准类型取代
假设Students表的属性Sex用SexType域定义
DROP DOMAIN SexType RESTRICT
不能删除SexType域
DROP DOMAIN SexType CASCADE
将删除SexType域，并将Students表中的Sex的定义修改为
Sex CHAR(2) CHECK (Sex IN (‘男’, ‘女’))②属性约束
- 属性上的约束是指属性的取值必须来自其定义的值域
- 例如，如果可以规定学生成绩的取值范围为0~100，性别的取值为“男”或“女”等
- 插入新元组和修改元组的属性值可能导致违反属性约束
- DBMS应当在插入或修改前进行属性约束检查 违反属性约束，插入或删除将被拒绝
- SQL支持属性约束，在创建基本表时可以说明属性约束
- SQL还允许使用用户定义的域来说明属性
eg：
在前面的例子中，我们定义了PersonName域和SexType域。
我们可以在定义Teachers表时使用它们：
CREATE TABLE Teachers
(Tno CHAR (7) PRIMARY KEY,
Tname PersonName,
Sex SexType,
Birthday DATE,
Title CHAR (6),
Dno CHAR (4),
FOREIGN KEY (Dno) REFERENCES Departments (Dno)); ③关系约束
- 关系约束说明关系的合法取值，常常涉及同一关系的多个属性和/或多个元组
- 关系约束可以是静态元组约束，也可以是动态约束
- 静态约束是规定关系各个属性值之间应该满足的约束关系
- 例如，假设教师工资表包括职称、岗位津贴等属性。“教授的岗位津贴不低于1500元，不高于10000元”
- 动态约束是指修改元组的值时需要满足的约束条件
- 例如，在更新教师表时，工资、工龄这些属性值一般只会增加，不会减少
SQL支持关系约束
- 实体完整性是一种系统定义的关系约束。SQL允许在创建基本表时定义使用如下形式的CHECK子句定义一个或多个表约束：
- CHECK (<条件>) [<约束性质> ]
- <条件>的简单情况是涉及表属性的布尔表达式，而更复杂的情况可以涉SELECT查询。可选的<约束性质>指明约束是否可延迟，缺省时为不可延迟
eg
假设EmpSalary（职工工资）表具有如下模式：
EmpSalary (Eno, Ename, Dno, BaseSalary, Subsidy, Bonus, Tax, Insurance, PayLoan)
CREATE TABLE EmpSalary
(Eno CHAR (7) PRIMARY KEY,
Ename CHAR (8) NOT NULLL,
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));
④断言与数据库约束
- 断言（Assertions）是一种命名约束，它表达了数据库状态必须满足的逻辑条件
- 断言被用来表达数据库约束，这些约束不能或很难用其他方法表达。例如，约束“每个支行的贷款金额总和必须小于或等于该支行客户存款金额总和”涉及多个关系，很难用前面介绍的方法表达
- SQL创建断言的语句具有如下格式：
- CREATE ASSERTION <断言名>
- CHECK (<条件>) [<约束性质> ]
eg
设数据库包含如下关系模式：
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)));
⑤触发器
- 触发器（trigger）是特殊类型的存储过程，当某个事件发生时它自动执行 要设置触发器机制，必须满足两个要求：
- (1) 指明什么事件发生和满足什么条件执行触发器；
- (2) 指明触发器执行什么样的动作
- 这种模型称作事件-条件-动作模型
- 数据库系统将像保存数据一样存储触发器。只要指定的事件发生，触发条件满足，相应的存储过程就被执行
触发器的其他作用
- 例如，一个定时触发器可以在每个周末主动地制作某些定制的报表，而不必在用户要求之后才被动地完成这些任务
为什么需要触发器？
- DBMS虽然提供了多种完整性约束定义和检查机制。然而，除了违反参照完整性约束的删除和修改进行级联删除和修改外，违反其他约束的更新都被简单地拒绝
- 触发器对示警或满足特定条件时自动执行某项任务是非常有用的，对实现复杂的完整性约束（如参照完整性不能覆盖的复杂约束）也是有用的
两个例子： - 银行的透支处理：银行允许用户透支，但存款余额不能取负值。透支（存款余额小于零）时可以将存款余额置零，并同时建立一笔贷款，其金额等于透支额。这样，当存款余额更新后小于零时不是简单地拒绝更新，而是触发一系列动作完成上述任务，同时避免破坏完整性约束
- 存货预警：当库存量下降到最小库存量以下时，自动提示仓库管理人员订货或自动产生一个订单。自动触发一个过程来完成上述任务
SQL中的触发器：
-
创建触发器语句的一般格式如下：
- CREATE TRIGGER <触发器名> <触发时间> <触发事件> ON <表名>
- [REFERENCING <旧/新值别名>, …,<旧/新值别名>]
- [FOR EACH {ROW | STATEMENT}]
- [WHEN (<触发条件>)] <被触发的SQL语句>
-
该语句在<表名>（记作T）上创建一个<触发器名>的触发器 <触发时间>可以是 BEFORE：事件前， AFTER：事件后
-
<触发事件>可以是T上的INSERT、DELETE、UPDATE或 UPDATE OF <触发列>, …, <触发列>， 这里<触发列>是表T的属性
-
REFERENCING子句创建一些过渡变量用来存放表T和表T的行更新前的旧值和更新后的新值。这些变量范围是该触发器，可以在触发动作体的语句中引用
-
<旧/新值别名>可以是如下形式之一
- OLD/NEW [ROW] [AS] <变量>：创建行过渡变量<变量>存放表T的行更新前/后的值
- OLD/NEW TABLE [AS] <变量>：创建表过渡变量<变量>存放表T更新前/后的值
-
FOR EACH ROW 定义行级触发器（每个行更新都触发），而FOR EACH
STATEMENT定义语句级触发器（每个更新语句触发一次）。缺省时为语句级触发器 -
WHEN子句说明触发条件，缺省时无条件触发。<触发条件>是一个任意布尔表达式
-
<被触发的SQL语句>是触发动作体，具有如下形式：
- BEGIN ATOMIC
<可执行的SQL语句>;
…
END
- BEGIN ATOMIC
-
删除触发器直接使用如下形式的语句：DROP TRIGGER <触发器名>
eg
设银行数据库包含如下关系模式：
Branch (BranchName, BranchCity, Assets)
Customer (CustomerName, CustomerStreet, CustomerCity)
Account (AccountNumber, BranchName, Balance)
Loan (Loan-number, BranchName, Amount)
Depositor (CustomerName, AccountNumber)
Borrower (CustomerName, LoanNumber)
考虑银行的透支处理。当存款关系Account的某元组t的Balance修改后的值小于零时，系统做如下三件事：
向贷款关系Loan插入一个新元组t1，其中t1.LoanNumber= t.AcountNumber， t1.BranchName = t.BranchName, t1.Balance等于透支额
向Borrower插入新元组t2 ，记录新的贷款与顾客之间的联系。假设记录存款关系Account元组t与顾客之间的联系的Depositor元组为t3 ，则t2的两个属性都取自t3
将Account的元组t的Balance置零
使用SQL，我们可以建立如下触发器OverdraftTrigger：
CREATE TRIGGER OverdraftTrigger
AFTER UPDATE OF Balance ON Account
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.Balance < 0)
BEGIN ATOMIC
INSERT INTO Loan
VALUES (nrow.AccountNumber, nrow.BranchName, – nrow.Balance);
INSERT INTO Borrower
(SELECT CustomerName, AccountNumber
FROM Depositor
WHERE nrow.AccountNumber = Depositor.AccountNumber);
UPDATE Account SET Balance = 0
WHERE Account.AccountNumber = nrow.AccountNumber
END 
715
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
