简介： 约束是保证数据完整性的一种数据库对象，按约束作用不同，分为七种。

约束是保证数据完整性的一种数据库对象，按约束作用不同，分为七种。

约束从字面上来看就是受到限制，它是附加在表上，通过限制列中、行中、表之间数据来保证数据完整性的一种数据库对象。

在 MySQL 中，有多种约束，可按以下不同方式进行分类。

● 按约束的应用范围不同，约束可分为列级约束和表级约束。列级约束是数据表中列定义的一部分，只能作用于表中的一列；表级约束独立于列定义之外，作用于表中的多列。当一个约束中必须包含多个列时，必须使用表级约束。

● 按约束的作用不同，约束可分为主键约束(PRIMARY KEY)、唯一性约束(UNIQUE)、外键约束(FOREIGN KEY)、默认值约束(DEFAULT)、非空约束(NOT NULL)、自增特性(AUTO_INCREMENT)和检查约束(CHECK)。

提示/

给约束定义的名称，称之为约束名。约束名可以由用户自己指定，也可由系统指定。对于约束名的命名推荐为 type_table_column，其中 type 表示约束的类型，table 为表名，column 为列名，例如：PK_books_bookID 表示在图书表 books 的 bookID 列上创建了主键约束。

约束创建的时机，分为以下两种。

(1) 在建表的同时创建约束。

(2) 建表后(修改表)创建约束。

01、主键约束

主键约束(PRIMARY KEY)是在表中定义一个主键来唯一确定表中的每一行记录。主键可以定义在单列上，也可以定义在多列上。该约束通过主键索引来强制实体完整性。

主键约束具有以下特点。

每个表最多只能定义一个主键约束，外键约束使用它作为维护数据完整性的参考点。

● 主键约束所在列不允许输入重复值。如果主键约束由两个或两个以上的列组成，则该组合的取值不重复。

● 在主键约束中定义的所有列都必须定义为非空(NOT NULL)。

● 主键约束名总为 PRIMARY，所以不需要指定约束名。

● 主键约束在指定的列上创建了一个主键索引，索引名默认为 PRIMARY。

● 关系模型理论要求为每个表定义一个主键，但 MySQL 并没有这样的要求，可以创建一个没有主键的表，但是从安全角度考虑应该为每个表指定一个主键。

当在一个已经存放了数据的表上增加主键约束时，MySQL 会自动对表中的数据进行检查，以确保这些数据能够满足主键约束的要求，即设定主键约束的列的所有数据值必须唯一，否则系统会返回错误信息，并拒绝执行增加约束的操作。

主键约束的基本语法格式如下所示。

语法说明如下。

如果定义的是列级约束，则不需要指定列名 column，只需在列定义的后面加上 PRIMARY KEY。

如果定义的是表级约束，则需要指定主键所在列名，在表定义语句后，加上该子句。

提示/

创建主键约束时系统会自动创建一个主键索引，该索引不同于手工创建的索引，不能使用 DROP INDEX 语句直接删除，只有删除主键约束，才能删除其相应的索引。

如果有外键约束正在参考主键约束中的数据，那么这些主键约束中的数据便不能被修改，也不能被删除。但是，如果在创建外键约束时，指定了级联操作子句，就可以修改或删除主键约束中的数据了。

1. 创建数据表时添加主键约束

在创建数据表时可以将一列或多列的组合设置为主键约束，该约束由系统提供主键约束名 PRIMARY，因此即便人工设置约束名的命令可以成功运行，但系统仍然将 PRIMARY 作为主键约束名。

【例 6-25】在图书销售数据库 booksale 中创建图书表 books2，其中将 bookid 列设置为主键，然后查看约束信息及索引情况。

可以从 information_schema 架构下的系统表查看约束。其中 CONSTRAINT_NAME 列为约束名，CONSTRAINT_TYPE 列为约束类型。创建 books2 表的同时创建了一个主键约束，这个主键约束是一个列级约束，默认的主键约束名为 PRIMARY，约束类型为 PRIMARY KEY。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为 PRIMARY，索引关键字是 bookid。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 1062-Duplicate entry '1' for key 'books2.PRIMARY'。因为 bookid 的值“1”是重复的，违反了主键约束。

【例 6-26】在图书销售数据库 booksale 中创建订单项目表 orderitems2，该表的主键约束设置在 orderid 和 bookid 两列上，然后查看约束信息及索引情况。

创建 orderitems2 表的同时创建了一个主键约束，这个主键约束是一个表级约束，默认的主键约束名为 PRIMARY，约束类型为 PRIMARY KEY。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为 PRIMARY，索引第一关键字是 orderid，第二关键字是 bookid。

输入以下数据进行验证。

插入成功，因为主键建立在 orderid 和 bookid 两列上，因此只有 orderid 列值相同或 bookid 列值相同不违反主键约束，只有 orderid 和 bookid 两列上的值都重复时，才会违反主键约束，导致插入失败。

2. 修改数据表时添加主键约束

如果创建数据表时没有指定主键约束，可以在修改数据表时设置主键约束。

【例 6-27】在图书销售数据库 booksale 的表 categories 中，为 ctgcode 列添加主键约束。

系统默认的主键约束名为 PRIMARY。若 categories 表的 ctgcode 列所有取值均唯一，则主键约束可以成功建立，否则将会创建失败。

提示/

在修改表时添加主键需要注意，由于表中已经存在数据，若设置主键的列存在违反实体完整性的情况，则主键约束无法创建成功。

3. 删除主键约束

一个表只允许有一个主键约束，对于已存在的主键约束，可以修改或删除它。例如：要将其他列或列组合设为主键约束，必须先删除现有的主键约束，然后再重新创建。

【例 6-28】在图书销售数据库 booksale 的表 categories 中，删除现有的主键约束。

主键约束删除的同时，自动生成的主键索引也同步删除。

02、唯一性约束

唯一性约束(UNIQUE)是用来保证数据表中的一列或多列中的数据是唯一的。该约束通过唯一性索引来强制实体完整性。当表中已经存在主键约束时，如果需要在其他列上实现实体完整性，由于一个表中只能有一个主键约束，因此可以通过创建唯一性约束来实现。

当在一个已经存放了数据的表上增加唯一性约束时，MySQL 会自动对表中的数据进行检查，以确保这些数据能够满足唯一性约束的要求，即设定唯一性约束的列除 NULL 外，所有数据的值必须唯一，否则系统会返回错误信息，并拒绝执行增加约束的操作。

唯一性约束具有以下特点。

● 每个表可以定义多个唯一性约束，且多个唯一性约束的列可以重合。

● 唯一性约束所在列不允许输入重复值。如果唯一性约束由两个或两个以上的列组成，则该组合的取值不重复。

● 唯一性约束所在列允许取空值，但必须用 NULL 声明。不过，当和参与唯一性约束的任何值一起使用时，每列只允许一个空值。

● 唯一性约束在指定的列上创建了一个唯一性索引。

提示/

主键约束和唯一性约束的区别是：一个表只允许建立一个主键约束，而唯一性约束可以建立多个；主键约束的关键列不允许取空值，而唯一性约束的关键列允许取空值；主键约束默认创建的是主键索引，唯一性约束默认创建的是唯一性索引。

唯一性约束的基本语法格式如下所示。

语法说明如下。

constraint_name 是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以被约束列名命名的约束名称。约束名称最大长度为 64 个字符，而且区分大小写。

UNIQUE 是定义唯一性约束的命令关键字。

如果定义的是列级约束，且不需要指定约束名，则不需要指定列名 column，只需在列定义的后面加上 UNIQUE。

如果定义的是表级约束，或是列级约束但要指定约束名，则需要指定唯一性约束所在列名，在表定义语句后，加上该子句。

1. 创建数据表时添加唯一性约束

在创建数据表时可以为一列或多列的组合设置唯一性约束，该约束可由系统提供唯一性约束名，也可由用户指定唯一性约束名。

【例 6-29】在图书销售数据库 booksale 中创建图书类别表 categories2，为 ctgcode 列创建唯一性约束，然后查看约束信息及索引情况。

创建 categories2 表的同时创建了一个唯一性约束，这个唯一性约束是一个列级约束，默认的约束名为被约束列名 ctgcode，约束类型为 UNIQUE。创建该唯一性约束的同时还创建了一个唯一性索引，索引和约束同名为 ctgcode，索引关键字是 ctgcode。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：

因为 ctgcode 的值'computer'是重复的，违反了唯一性约束。

【例 6-30】在图书销售数据库 booksale 中创建顾客表 customers2，为 emailaddress 列设置唯一性约束，约束名为 UN_customers2_emailaddress，然后查看约束信息及索引情况。

创建 customers2 表的同时创建了一个主键约束和一个唯一性约束，这两个约束都是列级约束，主键约束名为系统默认的约束名 PRIMARY，约束类型为 PRIMARY KEY，唯一性约束名为指定的约束名 UN_customers2_emailaddress，约束类型为 UNIQUE。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为 PRIMARY，索引关键字是 cstid。创建该唯一性约束的同时还创建了一个唯一性索引，索引和约束同名为 UN_customers2_emailaddress，索引关键字是 emailaddress。

2. 修改数据表时添加唯一性约束

如果创建数据表时没有指定唯一性约束，可以在修改数据表时设置唯一性约束。

【例 6-31】在图书销售数据库 booksale 的表 books2 中，为 isbn 列设置唯一性约束。

默认的约束名为被约束列名 isbn。若 books2 表的 isbn 列所有取值均唯一，则唯一性约束可以成功建立，否则将会创建失败。

提示/

在修改表时添加唯一性约束需要注意，由于表中已经存在数据，若设置唯一性约束的列存在违反实体完整性的情况，则唯一性约束无法创建成功。

3. 删除唯一性约束

一个表可以含有多个唯一性约束，对于已存在的唯一性约束，可以修改或删除它。若要修改唯一性约束，必须先删除现有的唯一性约束，然后再重新创建。

【例 6-32】在图书销售数据库 booksale 的表 books2 中，删除唯一性约束。

唯一性约束删除的同时，自动生成的唯一性索引也同步删除。

03、外键约束

外键约束(FOREIGN KEY)是指用于建立和加强两个表之间的连接的一列或多列，即在某一列或多列的组合上定义外键约束，这些列值参考某个表中的主键约束列。该约束强制参考完整性。

定义主键约束的表称之为主键表或父表，定义外键约束的表称之为外键表或子表，外键表的被约束列的取值必须是主键表的被约束列的值或为空。

外键约束具有以下特点。

● 每个表可以定义多个外键约束。

● 临时表不能创建外键约束。

● 外键表中被约束的列必须和主键表中被约束的列数据类型一致、长度一致。

● 外键约束将自动创建索引。

● 外键约束的主要目的是控制可以存储在外键表中的数据，但它还可以控制对主键表中数据的更改。

● 根据参照动作，可以控件父表数据的删除。

外键约束的基本语法格式如下所示。

语法说明如下。

●constraint_name 是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以表名开头、加上“_ibfk_”以及一个数字编号(1，2，3，…)组成的约束名字。约束名称最大长度为 64 个字符，而且区分大小写。

● FOREIGN KEY 是定义外键约束的命令关键字。

● REFERENCES 用于指定该外键参考哪个父表中的哪个主键列或候选键列。

● ON DELETE 和 ON UPDATE 选项是通过使用级联参照完整性约束，定义当用户试图删除或更新现有外键指向的键时，数据库引擎将执行以下操作。

◇ RESTRICT 表示拒绝对父表进行删除或更新操作。

◇ CASCADE 表示如果在父表中删除或更新了一行，则将在引用表中删除或更新相应的行，即级联删除或级联更新。如果 timestamp 列是外键或被引用键的一部分，则不能指定 CASCADE。

◇ SET NULL 表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为 NULL。若要执行此约束，外键列必须可为空值。

◇ NO ACTION 与 RESTRICT 的作用相同，它是标准的 SQL 关键字。

◇ SET DEFAULT 表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为它们的默认值。若要执行此约束，外键列必须具有默认值定义。如果某个列可为空值，并且未设置显式的默认值，则会使用 NULL 作为该列的隐式默认值。

1. 创建数据表时添加外键约束

在创建数据表时可以添加外键约束。

【例 6-33】在图书销售数据库 booksale 中创建评论表 comments2，为 cstid 列添加外键约束，该列的取值要参考 customers 表中的 cstid 列，为 bookid 列添加外键约束，该列的取值要参考 books 表中的 bookid 列，该约束名为 FK_books_comments2_bookid，然后查看约束和索引的情况。

注意/

必须先创建父表 books，且 books 表的 bookid 列是主键，再创建子表 comments2。

对外键约束来说，虽然支持列级约束的创建语法，但实际上没有效果，因此在 cstid 列上创建外键约束的写法无法成功地创建外键约束，而在 bookid 列上创建外键约束的写法可以成功地创建外键约束。因此在创建 comments2 表的同时创建了一个外键约束，这个外键约束的约束名为指定约束名 FK_books_comments2_bookid，约束类型为 FOREIGN KEY。

创建该外键约束的同时还创建了一个索引，索引名同约束名为 FK_books_comments2_bookid，索引关键字是 bookid。

2. 修改数据表时添加外键约束

如果创建数据表时没有指定外键约束，可以在修改数据表时设置外键约束。如果已经创建了外键约束，但是没有加上级联功能，则需要先将该外键约束删除，然后重新建立外键约束时添加级联功能。

【例 6-34】在图书销售数据库 booksale 的表 comments2 中，为 cstid 列添加外键约束，该列的取值要参考 customers 表中的 cstid 列。如果删除或更新了 customers 表中的一条记录，则 comments2 表相关的记录也相应删除或更新，然后查看约束和索引的情况。

命令中未指定约束名，因此约束名由系统自动生成，名为"comments2_ibfk_1"。创建该外键约束的同时还创建了一个索引，当不指定约束名时自动生成的索引名为外键约束的列名 cstid，索引关键字是 cstid。

3. 删除外键约束

一个表可以含有多个外键约束，对于已存在的外键约束，可以修改或删除它。若要修改外键约束，必须先删除现有的外键约束，然后再重新创建。

【例 6-35】在图书销售数据库 booksale 的表 comments2 中，删除约束名为"comments2_ibfk_1"的外键约束。查看约束和索引的情况。

指定的外键约束已经删除，但外键约束创建时生成的索引不会自动删除。

04、默认值约束默认值约束(DEFAULT)通过设置默认值来强制域完整性。在表中的某个列上定义了默认约束后，当插入新的数据行时，如果没有为该列指定数据，则系统将默认值赋值给该列。

默认值约束具有以下特点。

● 表中的每个列上只能定义一个默认约束。

● 默认值只能是常量值和 CURRENT_TIMESTAMP(返回当前的日期和时间)。

● 默认值不能参照于其他列或其他表的值。

默认值约束的基本语法格式如下所示。

语法说明如下。

SET 是可选选项，当向已有的表中添加默认值约束时使用的命令关键字；新建表中添加默认值约束时不用该关键字。

DEFAULT 是定义默认值约束的命令关键字。

constant_expression 是默认值的常量表达式。此表达式若为文本字符串，请用单引号(‘)将值括起来。

该约束只能为列级约束，只需在列定义的后面加上该子句。

1. 创建数据表时添加默认值约束

在创建数据表时可以为指定列设置默认值约束。

【例 6-36】在图书销售数据库 booksale 中创建评论表 comments3，为 comment 列添加一个默认值约束，默认值设为 good。

输入以下数据进行验证。

第一条语句的 comment 列输入的空字符串(' ')，所以显示为空白；第二条语句的 comment 列没有输入值，但是显示记录中该列的值为 good，说明该记录自动使用了 comment 列的默认值。

【例 6-37】在图书销售数据库 booksale 中创建订单表 orders2，为 orderdate 列添加一个默认值约束，默认值设为创建时间。

默认值只能是常量值和 CURRENT_TIMESTAMP，且 CURRENT_TIMESTAMP 只适合 timestamp 数据类型。创建 orders2 表的同时创建了两个默认值约束，orderdate 列设置默认值为 CURRENT_TIMESTAMP，shipdate 列设置默认值为空。

输入以下数据进行验证。

order2 表中，orderdate 列显示当前系统日期，而 shipdate 列为空。

2. 修改数据表时添加默认值约束

如果创建数据表时没有指定默认值约束，可以在修改数据表时设置默认值约束。

【例 6-38】在图书销售数据库 booksale 的表 customers 表中，为 password 列添加一个默认值约束，默认值设为“12345678”，然后查看表结构。

3. 删除默认值约束

一个表可以含有多个默认值约束，对于已存在的默认值约束，可以修改或删除它。若要修改默认值约束，必须先删除现有的默认值约束，然后再重新创建。

【例 6-39】在图书销售数据库 booksale 的表 comments3 表中，删除建立在 comment 列上的默认值约束。

05、非空约束

非空约束(NOT NULL)将保证所有记录中该列都有值。在表中的某列上定义了非空约束后，当插入新数据行时，如果没有为该列指定数据，则数据库系统会报错。

非空约束具有以下特点。

● 列级约束，只能使用列级约束语法定义。

● 确保列值不允许为空。

提示/

所有数据类型的值都可以是 NULL 值；空字符串不等于 NULL，0 也不等于 NULL。

非空约束的基本语法格式如下所示。

语法说明如下。

NOT NULL 是设置非空约束的命令关键字。

该约束只能为列级约束，只需在列定义的后面直接添加该关键字，不添加该关键字时默认为 NULL。

1. 创建数据表时添加非空约束

在创建数据表时可以为指定列设置非空约束。

【例 6-40】在图书销售数据库 booksale 中创建图书类别表 categories3，为 ctgcode 列创建非空约束。

输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 1048-Column 'ctgcode' cannot be null。因为 ctgcode 列的值是 NULL，违反了非空约束。

2. 修改数据表时添加非空约束

如果创建数据表时没有指定非空约束，可以在修改数据表时设置非空约束。

【例 6-41】在图书销售数据库 booksale 的表 categories3 中，为 ctgname 列创建非空约束。

该方法既可改变列的数据类型，又可为列添加非空约束。

3. 删除非空约束

删除非空约束的方法其实就是修改数据表，为列设置属性 NULL。

【例 6-42】在图书销售数据库 booksale 的表 categories3 中，删除 ctgname 列上的非空约束。

06、自增约束

自增约束(AUTO_INCREMENT)是 MySQL 数据库中一个特殊的约束，其主要用于为表中插入的新记录自动生成唯一的 ID。

自增约束具有以下特点。

● 一个表只能有一个列使用自增约束，且该列必须是主键或主键的一部分。

● 自增列必须具备 NOT NULL 属性。

● 自增约束的列可以是任何整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。

● 默认情况下自增列中的第一个值是 1，后续值自动加 1。如果用户设置了一个非 1 的初始值，后续值将在该值基础上自动加 1。

● 自增数据列序号的最大值受该列的数据类型约束，如 TINYINT 数据列的最大编号是 127，若加上 UNSIGNED，则最大为 255。一旦达到上限，自增就会失效。

自增约束的基本语法格式如下所示。

语法说明如下。

AUTO_INCREMENT 是设置自增约束的命令关键字，在列的后面直接添加该关键字。

AUTO_INCREMENT=n 是可选选项，用于设置自增的初始值，设置在表结构的外面。省略时表示从 1 开始自增。

1. 创建数据表时添加自增约束

在创建数据表时可以为主键或主键的部分列设置自增约束。

【例 6-43】在图书销售数据库 booksale 中创建评论表 comments4，为 cmmid 列添加主键约束和自增约束，设置自增的初始值为 100。

cmmid 列的自动增长值为 100、101、102 等，以 100 为起始值间隔为 1 这样增长。

2. 修改数据表时添加自增约束

如果创建数据表时没有指定自增约束，可以在修改数据表时设置自增约束。

【例 6-44】在图书销售数据库 booksale 的表 books2 中，为 bookid 列创建自增约束。

前提是 bookid 列是主键或主键的一部分。

3. 删除自增约束

删除自增约束的方法其实就是修改数据表，去掉 AUTO_INCREMENT。

【例 6-45】在图书销售数据库 booksale 的表 books2 中，删除 bookid 列上的自增约束。

07、检查约束

检查约束(CHECK)是用来验证用户输入某一列的数据的有效性。该约束通过列中的值来强制域的完整性，它用来指定某列可取值的集合或范围。

检查约束具有以下特点。

● 每个表可以定义多个检查约束。

● 检查约束可以参考本表中的其他列。例如：在订单表 orders 中，shipdate(发货日期)列可以引用 orderdate(订购日期)列，使得 shipdate 列的数据大于 orderdate 列的数据。

● 检查约束不能放在 AUTO_INCREMENT 属性的列上或数据类型为 timestamp 的列上，因为这两种列都是自动插入数据的。

● 当向设有检查约束的表中插入记录或更新记录时，该记录中的被约束列的值必须满足检查约束条件，否则无法录入。

● 可以为列级完整性约束，也可以为表级完整性约束。

● 检查约束在 MySQL 8.0.16 版本中才实现了自动对写入的数据进行约束检查。

检查约束的基本语法格式如下所示。

语法说明如下。

constraint_name 是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以表名开头、加上"_chk_"以及一个数字编号(1，2，3，…)组成的约束名字。约束名称最大长度为 64 个字符，而且区分大小写。

CHECK 是定义检查约束的命令关键字。

expr 是一个布尔表达式，用于指定约束的条件。表中的每行数据都必须满足 expr 的结果为 TRUE 或 UNKNOWN(NULL)。如果表达式的结果为 FALSE，将会违反约束。

ENFORCED 是可选的子句，用于指定是否强制该约束：如果忽略或指定了 ENFORCED，创建并强制该约束；如果指定了 NOT ENFORCED，创建但是不强制该约束，这也意味着约束不会生效。

1. 创建数据表时添加检查约束

在创建数据表时可以添加检查约束。

【例 6-46】在图书销售数据库 booksale 中创建图书表 books3，其中 unitprice 列的取值范围在 0～200 元，ctgcode 列的取值只能是 computer 和 language。

创建 books3 表的同时创建了一个主键约束和两个检查约束，unitprice 列上的检查约束是一个列级约束，默认的约束名为 books3_chk_1，约束类型为 CHECK；ctgcode 列上的检查约束是一个表级约束，默认的约束名为 books3_chk_2，约束类型为 CHECK。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 3819-Check constraint 'books3_chk_1' is violated.。因为 unitprice 列的值 249.00 不满足检查约束的表达式，违反了检查约束。

2. 修改数据表时添加检查约束

如果创建数据表时没有指定检查约束，可以在修改数据表时设置检查约束。

【例 6-47】在图书销售数据库 booksale 的表 orders2 中，约定发货日期 shipdate 要在订购日期 orderdate 之后，因此为这两列设置检查约束。

若 books2 表的 shipdate 列取值均大于或等于 orderdate 列的取值，则检查约束可以成功建立，否则将会创建失败。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 3819 - Check constraint 'orders2_chk_1' is violated.。因为 shipdate 列的值"2021-04-14"小于 orderdate 列的值"2021-04-15"，不满足检查约束的表达式，违反了检查约束。

3. 删除检查约束

一个表可以含有多个检查约束，对于已存在的检查约束，可以修改或删除它。若要修改检查约束，必须先删除现有的检查约束，然后再重新创建。

【例 6-48】在图书销售数据库 booksale 的表 books3 中，删除建立在 ctgcode 列上的检查约束。