数据完整性与约束的分类

一、引言

为什么需要约束？为了保证数据的完整性。

（1）数据完整性

数据完整性指的是数据的精确性和可靠性。

为了保证数据的完整性，SQL对表数据进行额外的条件限制，从以下四方面考虑：

①实体完整性：同一张表中不能出现完全不能区分的记录

②域完整性：存在范围，不能无限

③引用完整性：引用的对象应当存在

④用户自定义完整性：例如密码不为空

二、约束

（1）什么是约束

约束是表级的强制规定。

理解为对表中字段的限制。

可以再=在创建表时规定约束（通过CREATE TABLE语句），或者在表创建之后，通过ALTER TABLE语句规定约束。

（2）约束的分类

①根据角度分类

单列约束与多列约束

②根据作用范围

列级约束（声明在对应字段之后）与表级约束（在所有字段之后）

③根据功能

非空约束（not null）

唯一性约束(unique)

主键约束(primary key)

外键约束(forengn key)

检查约束(check)

默认值约束(default)

(3)约束的添加

CREATE TABLE时添加约束

ALTER TABLE 时增加约束、删除约束

三、约束的具体讲解

（1）非空约束 NOT NULL

特点：限制某个字段/某列的值不能为空

注意：

①非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空

②一张表可以有多个列分别限定了非空

③空字符串不等于NULL，0也不等于NULL

举例：

NOT NULL只能使用列级约束，声明在对应的字段之后。

NOT NULL非空约束修饰的字段，不能写入NULL，（在没有设置默认值的情况下）默认值为NULL，所以也不能 不写入数据。

修改约束的时候，要注意修改之后的约束与表中已经存在的数据不能相悖，否则修改失败。

（2）唯一性约束 UNIQUE

特点：限制某个字段，不允许重复

①CREATE TABLE创建表时指明唯一性约束

唯一性约束可以直接在数据类型之后加上UNIQUE，但是如果需要对约束进行命名，则：

创建表时，最后一行写入：

CONSTRAINT 约束名 约束类型（被约束的字段）

举例：

创建唯一约束的时候，如果不给唯一约束命名，默认与列名相同。

②在ALTER TABLE修改表内容的时候，添加唯一约束

ALTER TABLE 表名

ADD UNIQUE(字段名);

也可以使用MODIFY的方式

ALTER TABLE 表名

MODIFY 字段名 数据类型（长度）UNIQUE；

如果需要自主为约束命名，则：

ALTER TABLE 表名

ADD CONSTRAINT 约束名 UNIQUE(字段名);

举例：

③对唯一性数据进行插入

受到唯一性约束的数据不允许重复。

但如果写入NULL，可以重复添加NULL。

④复合的唯一性约束

同时约束两个字段，此时只有两个字段同时相等时，才判定为’不唯一‘进行报错。

⑤删除唯一性约束

ALTER TABLE 表名

DROP INDEX 唯一索引名

（唯一索引名即为约束名，可以是自主起名，默认则为列名。）

添加唯一性约束的列上回自动创建唯一索引

删除唯一约束只能通过删除唯一索引

删除时需要指定唯一索引名，唯一索引名和唯一约束名一样

如果创建唯一约束没有进行取名，则默认使用列名作为唯一索引名。

（3）主键约束 PRIMARY KEY

特点；唯一约束+非空约束

注意：

①一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。

②主键约束对应着表中的一列或者多列（复合主键）

③如果是多列组合的复合主键约束，这些列都不允许为空值，且组合的值不允许重复

④MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用

⑤当创建主键约束时，系统会默认在所在的列或列组合上建立对应的主键索引

⑥不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，有可能会破坏数据的完整性

①创建主键约束

创建主键约束一般在创建表的时候就完成创建。

可以直接在字段后添加PRIMARY KEY

也可以在CREATE TABLE的最后一行加上

PRIMARY KEY(字段)

等价于

MySQL中，修改表时也能够添加主键约束，但是不推荐。

ALTER TABLE 表名

ADD PRIMARY KEY(字段名)

或者使用MODIFY。

②复合主键约束

CREATE TABLE 表名(

字段名 数据类型，

字段名 数据类型，

…，

PRIMARY KEY(字段1，字段2)

)；

表示字段1，字段2的组合唯一，且字段1与字段2都非空。

只要有一个字段为NULL，操作失败。

③删除主键约束 （实际开发中用不到。）

ALTER TABLE 表名

DROP PRIMARY KEY

（4）自增列 AUTO_INCREMENT

作用：某个字段的值自增

特点和要求：

①一张表最多只能有一个自增列

②当需要产生唯一标识符或顺序值时，可设置自增

③自增长列约束的列必须是键列

④自增约束的列的数据类型必须是整数类型

⑤如果自增列指定了0和NULL，会在当前最大值的基础上自增；如果自增列手动制定了具体值，直接赋值为具体值。

开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则添加数据时，不给对应字段赋值。

①创建自增列

创建主键同时指明自增列

已有主键时添加AUTO_INCREMENT

②删除自增列

ALTER TABLE 表名

MODIFY 字段名 字段类型;

③MySQL8.0新特性：自增列的持久化

即使删除了已经创建的自增列的数据，并且重启MySQL，仍然从已经创建的自增列数据开始向后偏移，而不是从表中已有的数据开始向后偏移。

（5）外键约束 FOREIGN KEY

作用：限定某张表的某个字段的完整性

主表：被引用的表

从表：引用别人的表

特点：

-1-  被引用的数据应当是唯一的

-2-  如果不给外键约束命名，会自动产生一个外键名

-3-  应当先创建主表

-4-  应当先删除从表

-5-  从表中指定外键约束，一个表可以建立多个外键约束

-6-  从表的外键列与主表被参照的列名可以不相同，但是数据类型必须一致，逻辑意义一致

-7-  创建外键约束时，系统默认会在所在列上建立对应的普通索引。索引名是列名，不是外键的约束名

-8- 删除外键约束后，必须手动删除对应的索引

书写格式：

主表正常书写。

CREATE TABLE 表名（

字段 数据类型 约束声明，

字段 数据类型 约束声明，

……）

从表：

CREATE TABLE 表名（

字段 数据类型 约束声明，

字段 数据类型 约束声明，

……，

CONSTRAINT 外键约束名 FOREIGN KEY(从表字段) REFERENCES 主表名（主表对应的字段））

或者，在两张表书写完成之后，通过ALTER TABLE的方式，添加外键约束。

书写格式：

ALTER TABLE 表名

ADD CONSTRAINT 外键约束名 FOREIGN KEY(从表字段) REFERENCES 主表名（主表对应的字段）;

举例：

约束等级

Cascade：父表上更新与删除，子表同步更新

Set null：父表上更新与删除，子表对应字段更新为NULL

No action/Restrict:如果子表有匹配的记录，不允许父表进行更新与删除（默认）

ON UPDATE CASCODE ON DELETE SET NULL

ON UPDATE CASCODE ON DELETE RESTRICT

（写在从表最后一行外键约束之后）

删除外键约束

ALTER TABLE 表名

DROP FOREIGN KEY 外键约束名

再手动删除外键约束对应的普通索引

SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 表名

DROP INDEX 外键约束名。

小结：因为应用层面需保证完整性，在MySQL中允许使用但不推荐。

MySQL中，外键约束有成本，消耗系统资源。

外键和级联适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险，外键影响数据库的插入速度。

（6）检查约束CHECK

作用：检查输入的数据是否符合要求

MySQL5.7不支持CHECK约束，但是MySQL8.0支持。

 

（7）默认值约束 DEFAULT

作用：提供默认值

建表时提供默认值约束：

对已有的表的字段修改默认值约束：

需要注意，此时使用MODIFY进行修改，如果原本存在其他约束例如非空约束，需要保留写下，否则视为删除。