MySQL笔记-10 数据完整性约束

数据完整性是指数据的正确性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。在MySQL中提供了多种完整性约束，它们作为数据库关系模式定义的一部分，可以通过CREATE TABLE或ALTER TABLE语句来定义。一旦定义了完整性约束，MySQL服务器会随时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性与正确性。这样，既能有效地防止对数据库的意外破坏，又能提高完整性检测的效率，还能减轻数据库编程人员的工作负担。

1 定义完整性约束

关系模型的完整性规则是对关系的某种约束条件。在关系模型中，提供了实体完整性、参照完整性和用户定义完整性3项规则。下面将分别介绍MySQL中对数据库完整性3项规则的设置和实现方式。

1.1 实体完整性

实体(Entity)

是一个数据对象，是指客观存在并可以相互区分的事物，如一个教师、一个学生或一个雇员等。一个实体在数据库中表现为表中的一条记录。通常情况下，它必须遵守实体完整性规则。

实体完整性规则（Entity Integrity Rule）

是指关系的主属性，即主码（主键）的组成不能为空，也就是关系的主属性不能是空值（NULL）。关系对应于现实世界中的实体集，而现实世界中的实体是可区分的，即说明每个实例具有唯一性标识。在关系模型中，是使用主码（主键）作为唯一性标识的，若假设主码（主键）取空值，则说明这个实体不可标识，即不可区分，这个假设显然不正确，与现实世界应用环境相矛盾，因此不能存在这样的无标识实体，从而在关系模型中引入实体完整性约束。

例如，学生关系（学号、姓名、性别）中，“学号”为主码（主键），则“学号”这个属性不能为空值，否则就违反了实体完整性规则。

在MySQL中，实体完整性是通过主键约束和候选键约束来实现的。

1.1.1 主键约束

主键可以是表中的某一列，也可以是表中多个列所构成的一个组合；其中，由多个列组合而成的主键也称为复合主键。在MySQL中，主键列必须遵守以下规则:

1. 每一个表只能定义一个主键。
2. 唯一性原则。主键的值，也称键值，必须能够唯一标识表中的每一行记录，且不能为NULL。也就是说一张表中两个不同的行在主键上不能具有相同的值。
3. 最小化规则。复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的。
4. 一个列名在复合主键的列表中只能出现一次。

在MySQL中，可以在CREATE TABLE或者ALTER TABLE语句中，使用PRIMARY KEY子句来创建主键约束，其实现方式有以下两种:

• 1）作为列的完整性约束
  在表的某个列的属性定义时，加上PRIMARY KEY关键字实现。

create table tb_user(
id int auto_increment primary key，
user varchar(30) not null，
password varchar(30) not null，
createtime datetime);

• 2）作为表的完整性约束
  在表的所有列的属性定义后，加上PRIMARY KEY(index_col_name，…)子句实现。

create table tb_student (
id int auto_increment，
name varchar(30) not null，
sex varchar(2)，
classid int not null，
birthday date，
PRIMARY KEY (id，classid)
);

如果主键仅由表中的某一列所构成，那么以上两种方法均可以定义主键约束；如果主键由表中多个列所构成，那么只能用第二种方法定义主键约束。另外，定义主键约束后，MySQL会自动为主键创建一个唯一索引，默认名为PRIMARY，也可以修改为其他名称。

1.1.2 候选键约束

如果一个属性集能唯一标识元组，且又不含有多余的属性，那么这个属性集称为关系的候选键。例如，在包含学号、姓名、性别、年龄、院系、班级等列的“学生信息表”中，“学号”能够标识一名学生，因此，它可以作为候选键，而如果规定，不允许有同名的学生，那么姓名也可以作为候选键。

候选键可以是表中的某一列，也可以是表中多个列所构成的一个组合。任何时候，候选键的值必须是唯一的，且不能为空（NULL）。

候选键可以在CREATE TABLE或者ALTER TABLE语句中使用关键字UNIQUE来定义，其实现方法与主键约束类似，也是可作为列的完整性约束或者表的完整性约束两种方式。

在MySQL中，候选键与主键之间存在以下两点区别:

• 一个表只能创建一个主键，但可以定义若干个候选键。
• 定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

create table tb_user1(
id int auto_increment UNIQUE，
user varchar(30) not null UNIQUE，
password varchar(30) not null，
createtime TIMESTAMP default CURRENT_TIMESTAMP);

1.2 参照完整性

现实世界中的实体之间往往存在着某种联系，在关系模型中，实体及实体间的联系都是用关系来描述的，那么自然就存在着关系与关系间的引用。

例如，学生实体和班级实体可以分别用下面的关系表示，其中，主码（主键）用下划线标识。

• 学生（++学生证号++，姓名，性别，生日，班级编号，备注）
• 班级（++班级编号++，班级名称，备注）

在这两个关系之间存在着属性的引用，即“学生”关系引用了“班级”关系中的主码（主键）“班级编号”。在两个实体间，“班级编号”是“班级”关系的主码（主键），也是“学生”关系的外部码（外键）。

显然，“学生”关系中的“班级编号”的值必须是确实存在的班级的“班级编号”，即“班级”关系中的该班级的记录。也就是说，“学生”关系中某个属性的取值需要参照“班级”关系的属性和值。

参照完整性规则（Referential Integrity Rule）

就是定义外码（外键）和主码（主键）之间的引用规则，它是对关系间引用数据的一种限制。

参照完整性的定义为：

若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码K相对应，则对于R中每个元组在F上的值只允许两种可能，即要么取空值（F的每个属性值均为空值），要么等于S中某个元组的主码值。其中，关系R与S可以是不同的关系，也可以是同一关系，而F与K是定义在同一个域中。

例如，在“学生”关系中每个学生的“班级编号”一项，要么取空值，表示该学生还没有分配班级；要么取值必须与“班级”关系中的某个元组的“班级编号”相同，表示这个学生分配到某个班级学习。这就是参照完整性。

如果“学生”关系中，某个学生的“班级编号”取值不能与“班级”关系中任何一个元组的“班级编号”值相同，表示这个学生被分配到不属于所在学校的班级学习，这与实际应用环境不相符，显然是错误的，这就需要在关系模型中定义参照完整性进行约束。

与实体完整性一样，参照完整性也是由系统自动支持的，即在建立关系（表）时，只要定义了“谁是主码”“谁参照于认证”，系统将自动进行此类完整性的检查。

在MySQL中，参照完整性可以通过在创建表（CREATE TABLE）或者修改表（ALTER TABLE）时定义一个外键声明来实现。

MySQL有两种常用的引擎类型（MyISAM和InnoDB），目前，只有InnoDB引擎类型支持外键约束。InnoDB引擎类型中声明外键的基本语法格式如下：

[CONSTRAINT [SYMBOL]]
FOREIGN KEY (index_col_name，…) reference_definition

reference_definition主要用于定义外键所参照的表、列、参照动作的声明和实施策略等4部分内容。它的基本语法格式如下:

REFERENCES tbl_name [(index_col_name，…)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]

index_col_name的语法格式如下:

col_name [(length)] [ASC | DESC]

reference_option的语法格式如下:

RESTRICT | CASCADE | SET NULL | NO ACTION

参数说明如下:

1. index_col_name：用于指定被设置为外键的列。
2. tbl_name：用于指定外键所参照的表名。这个表称为参照表（或父表），而外键所在的表称作参照表（或子表）
3. col_name：用于指定被参照的列名。外键可以引用被参照表中的主键或候选键，也可以引用被参照表中某些列的一个组合，但这个组合不能是被参照表中随机的一组列，必须保存该组合的取值在被参照表中是唯一的。外键中的所有列值在被参照表的列中必须全部存在，也就是通过外键来对参照表某些列（外键）的取值进行限定与约束。
4. ON DELETE | ON UPDATE：指定参照动作相关的SQL语句。可为每个外键指定对应于DELETE语句和UPDATE语句的参照动作。
5. reference_option：指定参照完整性约束的实现策略。其中，当没有明确指定参照完整性的实现策略时，两个参照动作会默认使用RESTRICT。

具体的策略可选值如下：

可选值	说明
RESTRICT	限制策略：当要删除或更新被参照表中被参照列上，并在外键中出现的值时，系统拒绝对被参照表的删除或更新操作
CASCADE	级联策略：从被参照表中删除或更新记录行时，自动删除或更新参照表匹配的记录行
SET NULL	置空策略：当从被参照表中删除或更新记录行时，设置参照表中与之对应的外键列的值为NULL。这个策略需要被参照表中的外键列没有声明限定词NOT NULL
NO ACTION	不采取实施策略：当一个相关的外键值在被参照表中时，删除或更新被参照表中键值的动作不被允许。该策略的动作语音与RESTRICT相同

示例：

create table tb_student1 (
id int auto_increment，
name varchar(30) not null，
sex varchar(2)，
classid int not null，
birthday date，
remark varchar(100)，
primary key (id)，FOREIGN KEY (classid) REFERENCES tb_class(id) ON DELETE RESTRICTON UPDATE RESTRICT
);

要设置为主外键关系的两张数据表必须具有相同的存储引擎，如都是InnoDB，并且相关联的两个字段的类型必须一致。
设置外键时，通常需要遵守以下规则:

1. 被参照表必须是已经存在的，或者是当前正在创建的表。如果是当前正在创建的表，也就是说，被参照表与参照表是同一个表，这样的表称为自参照表（Self-referencing Table），这种结构称为自参照完整性（Self-referential Integrity）。
2. 必须为被参照表定义主键。
3. 必须在被参照表名后面指定列名或列名的组合。这个列或列组合必须是这个被参照表的主键或候选键。
4. 外键中列的数目必须和被参照表中的列的数据相同。
5. 外键中列的数据类型必须和被参照表的主键（或候选键）中的对应列的数据类型相同。
6. 尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

1.3 用户定义完整性

用户定义完整性规则（User-defined Integrity Rule）是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。关系模型提供定义和检验这类完整性规则的机制，其目的是由系统来统一处理，而不再由应用程序来完成这项工作。

在实际系统中，这类完整性规则一般是在建立数据表的同时进行定义，应用编程人员不需要再做考虑，如果某些约束条件没有建立在库表一级，则应用编程人员应在各模块的具体编程中通过程序进行检查和控制。

MySQL支持非空约束、CHECK约束和触发器3种用户自定义完整性约束。其中，触发器将在后续章节进行详细介绍。这里主要介绍非空约束和CHECK约束。

1.3.1 非空约束

在MySQL中，非空约束可以通过在CREATE TABLE或ALTER TABLE语句中，某个列定义后面加上关键字NOT NULL来定义，用来约束该列的取值不能为空。

CREATE TABLE tb_class1 (
id int(11) NOT NULL AUTO_INCREMENT，
name varchar(45) NOT NULL，
remark varchar(100) DEFAULT NULL，
PRIMARY KEY (`id`)
);

1.3.2 CHECK约束

与非空约束一样，CHECK约束也可以通过在CREATE TABLE或ALTER TABLE语句中，根据用户的实际完整性要求来定义。它可以分别对列或表实施CHECK约束，其中使用的语法如下:

CHECK(expr)

其中，expr是一个SQL表达式，用于指定需要检查的限定条件。在更新表数据时，MySQL会检查更新后的数据行是否满足CHECK约束中的限定条件。该限定条件可以是简单的表达式，也可以是复杂的表达式（如子查询）。

1.3.2.1 对列实施CHECK约束

将CHECK子句置于表的某个列的定义之后就是对列实施CHECK约束。

create table tb_student2 (
id int auto_increment，
name varchar(30) not null，
sex varchar(2)，
age int not null CHECK(age>6 and age<18)，
remark varchar(100)，
primary key (id)
);

MySQL8.0.16版本之前只是对CHECK约束进行了分析处理，但会被直接忽略，并不会报错。8.0.16版本及之后的版本才生效。

1.3.2.2 对表实施CHECK约束

将CHECK子句置于表中所有列的定义以及主键约束和外键定义之后就是对表实施CHECK约束。

create table tb_student3 (
id int auto_increment，
name varchar(30) not null，
sex varchar(2)，
classid int not null，
birthday date，
remark varchar(100)，
primary key (id)，
CHECK(classid IN (SELECT id FROM tb_class))
);

2 命名完整性约束

在MySQL中，也可以对完整性约束进行添加、修改和删除等操作。其中，为了删除和修改完整性约束，需要在定义约束的同时对其进行命名。命名完整性约束的方式是在各种完整性约束的定义说明之前加上CONSTRAINT子句实现的。CONSTRAINT子句的语法格式如下:

CONSTRAINT <symbol>
[PRIMAR KEY 短语 |FOREIGN KEY 短语 |CHECK 短语]

参数说明如下。

1. symbol：用于指定约束名称。这个名字是在完整性约束说明的前面被定义，在数据库里必须是唯一的。如果在创建时没有指定约束的名字，则MySQL将自动创建一个约束名字。
2. PRIMAR KEY短语：主键约束。
3. FOREIGN KEY短语：参照完整性约束。
4. CHECK短语：CHECK约束。

create table tb_student1a (
id int auto_increment PRIMARY KEY，
name varchar(30) not null，
sex varchar(2)，
classid int not null，
birthday date，
remark varchar(100)，CONSTRAINT fk_classid FOREIGN KEY (classid)
REFERENCES tb_class(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);

在定义完整性约束时，应该尽可能为其指定名字，以便在需要对完整性约束进行修改或删除时，可以很容易地找到它们。

只能给基于表的完整性约束指定名字，无法给基于列的完整性约束指定名字。

3 更新完整性约束

对各种约束命名后，就可以使用ALTER TABLE语句来更新或删除与列或表有关的各种约束。

3.1 删除完整性约束

在MySQL中，使用ALTER TABLE语句，可以独立地删除完整性约束，而不会删除表本身。如果使用DROP TABLE语句删除一个表，那么这个表中的所有完整性约束也会自动被删除。删除完整性约束需要在ALTER TABLE语句中使用DROP关键字来实现，具体的语法格式如下：

DROP [FOREIGN KEY| INDEX| <symbol>] |[PRIMARY KEY]

参数说明如下:

1. FOREIGN KEY：用于删除外键约束。
2. PRIMARY KEY：用于删除主键约束。需要注意的是，在删除主键时，必须再创建一个主键，否则不能删除成功。
3. INDEX：用于删除候选键约束。
4. symbol：要删除的约束名称。

示例：

ALTER TABLE tb_student1a DROP FOREIGN KEY fk_classid;

3.2 修改完整性约束

在MySQL中，完整性约束不能直接被修改，若要修改只能使用ALTER TABLE语句先删除除该约束，然后再增加一个与该约束同名的新约束。删除完整性约束的语法在3.1节已经介绍了，这里只给出在ALTER TABLE语句中添加完整性约束的语法格式，具体语法格式如下:

ADD CONSTRAINT <symbol> 各种约束

参数说明如下:

1. symbol：为要添加的约束指定一个名称。
2. 各种约束：定义各种约束的语句，具体内容请参见13.1和13.2节介绍的各种约束的添加语法。

示例：

ALTER TABLE tb_student1a DROP FOREIGN KEY fk_classid;
ALTER TABLE tb_student1a ADD CONSTRAINT fk_classid FOREIGN KEY (classid) REFERENCES tb_class(id) ON DELETE CASCADE ON UPDATE CASCADE;