数据库系统原理——数据库完整性（参考自王珊、萨师煊《数据库系统概论》）

数据库完整性

数据库完整性：数据库的完整性是指数据的正确性和相容性。数据的正确性是指数据是符合现实世界语义、反映当前实际状况的;数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。

数据的完整性和安全性是两个既有联系又不尽相同的概念。数据的完整性是为了防止
数据库中存在不符合语义的数据，**也就是防止数据库中存在不正确的数据。数据的安全性是保护数据库防止恶意破坏和非法存取。**因此，完整性检查和控制的防范对象是不合语义的、不正确的数据，防止它们进入数据库。安全性控制的防范对象是非法用户和非法操作，防止他们对数据库数据的非法存取。

一、实体完整性

1.关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。

【例1】将Student表中的Sno属性定义为码
①CREATE TABLE Student
（ Sno CHAR(9) PRIMARY KEY,
//在列级定义主码
Sname CHAR(20) NUT NULL,
Ssex CHAR(2)
);
②CREATE TABLE Student
（Sno CHAR(9),
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
PRIMARY KEY(Sno）
//在表级定义主码
）；

【例2】将SC表中的Sno、Cno属性定义为主码
CREATE TABLE SC
( Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno，Cno）
//只能在表级定义主码
）；

ps：由以上两个例子体现出了如果定义单个主码可以在表级也可以在列级定义主码，如果是有多个主码则只能在表级定义主码。

2.实体完整性检查和违约处理
定义了关系的主码后，每当用户程序对基本表插入一条记录或者对主码列进行更新才做时，关系数据库管理系统将自动进行检查，包括：
（1）检查主码值是否唯一，如果不唯一则拒绝插入或者修改。
（2）检查主码各个属性是否为空，只要一个为空就拒绝插入或者修改。

二、参照完整性
1.关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外
码，用REFERENCES短语指明这些外码参照哪些表的主码。
【例1】关系SC中一个元组表示一个学生选修的某门课程的成绩，(Sno, Cno)是主码
Sno、Cno分别参照引用Student表的主码和Course表的主码。
CREATE TABLE SC
( Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
//在表级定义实体完整性
FOREIGN KEY(Sno) REFERENCES Student (Sno),
//在表级定义参照完整性
FOREIGN KEY(Cno) REFERENCES Course(Cno)
//在表级定义参照完整性
);

2.参照完整性检查和违约处理
对被参照表和参照表进行增、删、改操作时有可能破坏参照完整性，必须进行检查以保证这两个表的相容性。对于参照完整性，除了定义外码，还应定义外码列是否允许控制。一般地，当对参照表和被参照表的操作违反了参照完整性时，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建参照表时显式地加以说明。

【例2】显式说明参照完整性的违约处理示例
CREATE TABLE SC
( Sno CHAR(9),
Cno CHAR(4)
Grade SMALLINT,
PRIMARY KEY(Sno,Cno)
//在表级定义实体完整性，Sno、Cno都不能取空值
FOREIGN KEY(Sno）REFERENCES Student（Sno）
//在表级定义参照完整性
ON DELETE CASCADE
//当删除Student表中的元组时，级联删除SC表中相应的元组
ON UPDATE CASCADE
//当更新Student表中的元组时，级联删除SC表中相应的元组
FOREIGN KEY(Cno)REFERENCES Course (Cno)
//在表级定义参照完整性
ON DELETE NO ACTION
//当删除Course表中的元组造成SC表不一致时，拒绝删除
ON UPDATE CASCADE
//当更新Course表中的Cno时，级联更新SC表中相应的元组
）；

三、用户定义完整性
1.属性上约束条件的定义
①列值非空（NOT NULL)
②列值唯一（UNIQUE)
③检查列值是都满足一个条件表达式（CHECK短语）
2.属性上约束条件的检查和违约处理
当往表中插入元组或者修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不被满足则操作拒绝执行。

四、完整性约束命名子句

1.完整性约束命名子句
CONSTRAINT<完整性约束条件名><完整性约束条件>
<完整性约束条件>包括NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK短语等。
【例1】建立学生登记表Student要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”
CREATE TABLE Student
（Sno NUMERIC(6)
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK(Sage<30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK(Ssex IN('男‘，’女‘）），
CONSTRAINT StudentKey PRIMARY KEY(Sno）
）；
2.修改表中的完整性限制
可以使用ALTER TABLE语句修改表中的完整性限制。

【例2】去掉对例1中队性别的限制
ALTER TABLE Student
DROP CONSTRAINT C4

5.域中的完整性限制
【例3】建立一个性别域，并声明性别域的取值范围。
CREATE DOMAIN GenderDomain CHAR(2)
CHECK(VALUE IN('男’，‘女’））；
这样就能对Ssex的说明改写为：
Ssex GenderDomain
【例4】建立一个性别域，并对其中的限制命名。
CREATE DOMAIN GenderDomain CHAR(2)
CONSTRAINT GD CHECK(VALUE IN('男‘，’女‘））；
【例5】把性别的取值范围由（’男‘，’女‘）改为（’1‘，’0‘）
ALTER DOMAIN GenderDomain
DROP CONSTRAINT GD;
ALTER DOMAIN GenderDomain
ADD CONSTRAINT GDD CHECK(VALUE IN(‘1’,‘0’).

五、断言

1.创建断言的语句格式CREATE ASSERTION<断言名><CHECK子句>
每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句中的约束条件表达式类似。

【例1】限制数据库课程最多60名学生选修。
CREATE ASSERTION AS_SC_Num
CHECK(60>=(SELECT COUNT(*)
FROM Course，SC
WHERE SC.Cno=Course.Cno AND Course.CNAME='数据库’）
）；

每当学生选修课程时，将在SC表中插入一条元组(Sno, Cno, NULL), ASSE
SC_ Num断言被触发检查。如果选修数据库课程的人数已经超过60人，CHECK子句
返回值为“假”，对SC表的插入操作被拒绝。

【例2】限制每一门课程最多60名学生选修
CREATE ASSERTION AS2
CHECK(60>=ALL(SELECT COUNT(*)
FROM SC
GROUP by Cno）
）；

【例3】限制每个学期每一门课程最多60名学生选修
首先修改SC表的模式，增加一个“学期（TEAM)"的属性。
ALTER TABLE SC ADD TEAM DATE
//TEAM的类型是DATE
然后定义断言：
CREATE ASSERTION ASS3
CHECK(60>=ALL(SELECT COUNT(*) FROM SC GROUP BY Cno,TEAM));

2.删除断言的语句格式

DROP ASSERTION<断言名>

六、触发器

1.SQL触发器，是一种特殊类型的存储过程，不由用户直接调用。它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。
DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生DML事件时启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。

a、在SQL SERVER 2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中，我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后，它们也就会被自动删除：INSERED表用于存放你在操件insert、update、delete语句后，更新的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表：DELETED表用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。

b、触发器可通过数据库中的相关表实现级联更改，可以强制比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列，例如触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作。触发器也可以根据数据修改前后的表状态，再行采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

c、与此同时，虽然触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用？过多触发器会造成数据库及应用程序的维护困难，同时对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。

DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生(DDL事件时启用。DDL事件即指在表或索引中的create、alter、drop语句也。

登陆触发器：是指当用户登录SQL SERVER实例建立会话时触发。

2.触发器语法

CREATE TRIGGER trigger_name

ON { table|view }

[ WITH ENCRYPTION ]

{

{ { FOR|AFTER|INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS

[ { IF UPDATE ( column )

[ { AND|OR } UPDATE ( column ) ]

[ ...n ]

|IF ( COLUMNS_UPDATED ( ) updated_bitmask )

column_bitmask [ ...n ]

} ]

sql_statement [ ...n ]

}

}

触发器部分参考来自：https://www.cnblogs.com/qwer123666/p/7083277.html