这是我参与8月更文挑战的第30天，活动详情查看：8月更文挑战

数据库提供的完整性约束条件

完整性约束条件也称为完整性规则，是数据必须满足的约束条件。

在RDBMS中一般通过DDL语句实现。

约束分为列级约束和表级约束。

比如主键约束PRIMARY KEY，可以定义在单个列表；也可以用表级别的语句定义（如多列主键）。

完整性检查机制一般在INSERT、UPDATE、DELETE语句执行后开始检查，也有的会在事务提交时检查。

数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查。

如果操作违背了完整性约束条件，就采取对应的违约处理，通常分为：

拒绝（NO ACTION）执行该操作。如，禁止插入数据。
级联（CASCADE）执行其他操作。如，级联删除外键引用数据，具体由RDBMS提供设置机制。

主键（实体完整性）

通过关键字PRIMARY KEY实现。

CREATE TABLE Student( 
 Sno CHAR(9) PRIMARY KEY, // 列级主键约束条件
 Sname CHAR(20) NOT NULL, 
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 // 或
 // PRIMARY KEY (Sno)   // 表级主键约束条件
);

CREATE TABLE SC( 
 Sno CHAR(9) NOT NULL, 
 Cno CHAR(4) NOT NULL, 
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno) /*联合主键，只能在表级定义主码*/
);

在插入行或者更新主键列时，RDBMS对实体完整性规则自动进行检查。

实体完整性的检查：

检查主码值是否唯一，不唯一则拒绝插入或修改；检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

检查主键值是否唯一的方法

查记录中主码值是否唯一的方法有多种，比如全表扫描、在主键上创建索引。

全表扫描

依次判断表中每一条记录的主码值与即将插入的主码值（或者修改的新主码值）是否相同。

表扫描最大的缺点就是：十分耗时，浪费资源，拖慢数据库性能。

索引

为避免对基本表进行全表扫描检查主键约束条件，现代RDBMS一般都会在主码上自动建立一个索引。

这就是为什么为数据表指定主键，会自动在主键上创建索引的原因。

在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。因此，也可以说，主键属于索引的一种。

主键索引在起到索引的作用（快速查找访问数据）同时，也用于对主键约束进行检查时，快速判断是否存在重复数据，做到主键值的唯一性且不能为空的要求。

主键约束（或主键索引）与唯一索引的区别，唯一索引允许为空（NULL），NULL没有唯一性的检查限制，即NULL可以有多个，但是唯一索引的其他值不能重复（因此，也叫唯一索引约束）。

主键约束不允许重复，也不允许为空。即等于 唯一索引（UNIQUE INDEX） + NOT NULL约束 的组合。

注：

唯一索引也是索引的一种，也可以只在表上添加唯一约束，这样也能实现列的值的唯一约束。

外键（参照完整性）

数据库中，通过外键约束实现数据的参照完整性。

外键的值要么取空值，要么取被引用（参照）的列的值（被引用的列通常是主键列，或者唯一索引（唯一约束）列（有的数据库管理系统提供该机制，有的只能允许引用主键列））。

实现参照完整性，就是要定义哪个属性是外码，外码参照的是哪个关系的主码。

定义外码和参照关系(列)分别使用 FOREIGN KEY 命令和 REFERENCES 命令

外键约束同样可以定义在列级上，或表级上。

例如：Student表的Sdept属性是外码，参照DEPT表的主码Deptno。

CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
    Sname CHAR(20) UNIQUE, 
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20) FOREIGN KEY REFERENCES DEPT(Deptno)  /*在列级定义参照完整性*/
    // 或 
    // FOREIGN KEY(Sdept) REFERENCES DEPT(Deptno)  /*在表级定义参照完整性*/
 );

DBMS什么时候进行参照完整性的检查？或，什么操作触发检查参照完整性？

参照完整性联系的是两个表。当对被参照表和参照表进行增删改操作时，有可能破坏参照完整性，必须进行检查。

外键约束的违约处理

可能破坏参照完整性的情况及违约处理：

被参照表（例如Student）	参照表（例如SC）	违约处理
	插入元组（插入外键值在被参照表中不存在）	拒绝
	修改外码值（修改外键值在被参照表中不存在）	拒绝
删除元组（该记录在参照表中被引用）		拒绝/级连删除/设置为空值
修改主码值（该记录在参照表中被引用）		拒绝/级连修改/设置为空值

参照完整性的违约处理和主键约束不同，除了拒绝，还可以采用级联操作。

具体如下：

（1）拒绝（NO ACTION）执行

不允许该操作执行。即禁止执行操作。

（2）级联（CASCADE）操作

当删除或修改被参照表（如Student）的一个元组造成了与参照表（如SC）的不一致，则删除或修改参照表中的所有造成不一致的元组。

（3）设置为空值（SET-NULL）

当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有不一致的元组的对应外码属性设置为空值。

显式设置外键约束的级联操作

如下，可以显式地设置违反外键约束时的级联操作或拒绝执行。

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)
      ON DELETE CASCADE /*级联删除SC表中相应的元组*/
      ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno) 
      ON DELETE NO ACTION /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
      ON UPDATE CASCADE /*当更新course表中的cno时，级联更新SC表中相应的元组*/
 );

用户定义的完整性

用户定义的完整性：针对某一具体应用的数据必须满足的语义要求。

关系数据库管理系统都会提供用户自己定义某种数据的完整性的机制，并在增、改时进行检查。而不需要应用程序承担对数据的检查处理。

用户定义完整性的违约处理和主键约束一样，拒绝执行操作。

属性上的约束条件

非空约束（NOT NULL），大多数数据库表的列取值默认NULL。
唯一约束（UNIQUE），注意和唯一索引的区别，通常使用唯一索引替代单纯的唯一约束。
默认值约束（DEFAULT）
检查列值是否满足一个条件表达式（CHECK），或取值范围。

使用check定义列值应该满足的取值条件。如下示例，Student表的Ssex只允许取“男”或“女”。：

CREATE TABLE Student( 
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL, 
  Ssex CHAR(2) CHECK (Ssex IN ('男','女'))， /*性别属性Ssex只允许取'男'或'女' */
  Sage SMALLINT,
  Sdept CHAR(20)
);

SC表中Grade分数只能为0~100：

CREATE TABLE SC( 
  Sno CHAR(9) ,
  Cno CHAR(4),
  Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)，/*Grade取值范围是0到100*/
  PRIMARY KEY (Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES Student(Sno),
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

会在插入元组或修改属性值时进行检查，如果违反则拒绝执行。

元组上的约束条件（表级别定义）

属性上的约束条件只能限制单个属性。而元组上的约束，可以设置不同属性之间的取值的相互约束条件。

通过CHECK子句定义（在表级别）元组上的约束条件。

如下，当学生的性别是男时，其名字不能以Ms.打头。

CREATE TABLE Student( 
  Sno CHAR(9), 
  Sname CHAR(8) NOT NULL，
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20),
  PRIMARY KEY (Sno),
  CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') /*定义了元组中 Sname 和 Ssex 两个属性值之间的约束条件*/
);

性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立; 当性别是男性时，要通过检查则名字一定不能以Ms.打头

会在插入元组或修改属性值时进行检查，如果违反则拒绝执行。

完整性约束命名子句——CONSTRAINT

CONSTRAINT子句可以用来对约束进行命名。

通常未指定名称时，数据库管理系统会为约束自动生成一个随机的名字（需要特定查找才能查询到名称）。

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

示例如下：创建学生表Student，要求学号是主键，且在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

CREATE TABLE Student( 
  Sno NUMERIC(6) CONSTRAINT CK_Student_Sno CHECK (Sno BETWEEN 90000 AND 99999),
 Sname CHAR(20) CONSTRAINT NOTNULL_Student_Sname NOT NULL,
 Sage NUMERIC(3) CONSTRAINT CK_Student_Sage CHECK (Sage < 30),
 Ssex CHAR(2) CONSTRAINT CK_Student_Ssex CHECK (Ssex IN ( ‘男’,'女')),
 CONSTRAINT PK_Student_Sno PRIMARY KEY(Sno)
 );

对约束进行命名，是为了方便进行修改或删除。

比如，删除上面的CK_Student_Sage约束：

ALTER TABLE Student DROP CONSTRAINT CK_Student_Sage;

修改完整性约束，可以先删除旧约束，再添加新约束：

ALTER TABLE Student
 DROP CONSTRAINT CK_Student_Sno;
ALTER TABLE Student
 ADD CONSTRAINT CK_Student_Sno CHECK (Sno BETWEEN 900000
 AND 999999);

Constraints enforce the data integrity to the database and protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server

Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data. IDENTITY columns and timestamp columns can't be associated with default constraint.

数据库完整性和安全性的区别

完整性要做的是防止出现不正确的、不符合语义的数据。

完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据。

安全性是要防止恶意的破坏和非法的存取数据，防范的是非法用户和非法操作。

安全性防范的是非法用户和非法操作存取数据库中的正确数据。

断言（ASSERTION）

断言介绍

除了上面介绍的实体完整性、参照完整性和用户定义的完整性约束之外，基本可以满足现实中的大部分需求。

但是，如果想要实现比这些更复杂的约束条件，可以使用断言——ASSERTION。

断言可以实现更具一般性的约束，比如涉及多个表的或聚集操作的比较复杂的完整性约束。

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

MySQL似乎未实现对断言的支持。

可以通过触发器实现断言的功能。

创建断言

CREATE ASSERTION<断言名><CHECK 子句>

<CHECK 子句>中的约束条件和平时SQL语句中的WHERE表达式基本相同。

断言示例

比如：

限制数据库课程最多60名学生选修。

这涉及到课程表、选修表，及聚合操作的判断。这是之前的约束条件无法实现的情况，只能使用断言。

如下，在断言的check子句中使用子查询，判断条件是否满足：

CREATE ASSERTION ASSE_SC_DB_NUM
  CHECK (60 >= (select count(*)
                  From Course,SC
                  Where SC.Cno=Course.Cno and 
                  Course.Cname ='数据库')
  );

限制每一门课程最多60名学生选修

CREATE ASSERTION ASSE_SC_CNUM1
  CHECK(60 >= ALL (SELECT count(*) 
                    FROM SC 
                    GROUP by cno)
  );

限制每个学期每一门课程最多60名学生选修

TERM为学期列：

CREATE ASSERTION ASSE_SC_CNUM2
  CHECK(60 >= ALL (SELECT count(*)
                    FROM SC
                    GROUP by cno,TERM)
  );

如果断言很复杂，则系统检测和维护断言的开销就很高

删除断言：

DROP ASSERTION <断言名>;

参考

主要参考自《数据库系统概论（基础篇）》

数据库教程28：数据库中完整性约束的实现和断言（ASSERTION）