关于约束

数据完整性是指数据的精确性和可靠性。防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或无效信息。

SQL规范以约束的方式对表数据进行额外的条件限制。

约束是表级的强制规定。可以在创建表时规定约束，或者在表创建之后通过对表的修改来规定约束。约束即对表中字段的限制。

约束的分类：

角度一：约束的字段的个数

单列约束、多列约束

角度二：约束的作用范围

列级约束、表级约束

列级约束：声明在对应字段的后面。

表级约束：声明在所有字段的后面。

角度三：约束的作用

①非空约束（NOT NULL）

②唯一性约束（UNIQUE）

③主键约束（PRIMARY KEY）

④外键约束（FOREIGN KEY）

⑤检查约束（CHECK）

⑥默认值约束（DEFAULT）

添加约束操作可以在创建表语句（CREATE TABLE）、修改表语句（ALTER TABLE）中。

删除约束操作在修改表语句（ALTER TABLE）中。

查看表约束：

SELECT * FROM information_schema.table_constraints

WHERE table_name = '表名';

非空约束

作用：

限制某个字段/某个列的值不允许为空（NULL）

关键字：NOT NULL

注：

非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空（非空约束只有列级约束，没有表级约束）。

空字符串''不等于NULL。

在创建表语句（CREATE TABLE）添加约束：

例：

CREATE TABLE test_1(

id INT NOT NULL,

last_name VARCHAR(15) NOT NULL,

email VARCHAR(25),

sal DECIMAL(10,2)

);

注：

此时添加记录时，如果id和last_name赋值为NULL，就会报错，添加失败。

当使用INSERT INTO 表名(字段名，字段名，……) VALUES (字段值，字段值，……); 的方式添加记录时，在表名后的括号中有未指明的字段时，添加的记录的未指明的字段要么默认为默认值，要么没有设置默认值，就会默认为NULL。如果此时未指明的字段没有设置默认值，且有非空约束，就会报错，添加失败。

在修改表语句（ALTER TABLE）中添加约束：

例：在MODIFY语句后写NOT NULL关键字即可

ALTER TABLE test_1

MODIFY email VARCHAR(25) NOT NULL;

在修改表语句（ALTER TABLE）中删除约束：

例：在MODIFY语句后不写NOT NULL关键字即可

ALTER TABLE test_1

MODIFY email VARCHAR(25);

唯一性约束：

用来约束某个字段/某列的值不能重复。（但是允许出现多个空值）

关键字：UNIQUE

特点：

同一个表可以有多个唯一约束。

唯一约束可以是某一个列的值唯一，也可以是多个列组合的值的唯一。

唯一性约束允许列值为空

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

MySQL会给唯一约束的列上默认创建一个唯一索引。

添加唯一性约束：

建表时：

CREATE TABLE test_2(

id INT UNIQUE NOT NULL,-- 列级约束

last_name VARCHAR(15) NOT NULL,

email VARCHAR(25) UNIQUE,-- 列级约束

sal DECIMAL(10,2),

phone_num VARCHAR(15),

UNIQUE(phone_num)-- 表级约束

);

改表时（ALTER TABLE）：

方式一：使用ADD UNIQUE（key）的方式。

例：

ALTER test_2

AND UNIQUE(sal);

方式二：使用MODIFY的方式。

例：

ALTER TABLE test_2

MODIFY last_name VARCHAR(15) UNIQUE;

复合的唯一性约束：（多列约束）

例：

CREATE TABLE test_user(

id INT,

`name` VARCHAR(15),

`password` VARCHAR(25),

#表级约束

UNIQUE(`name`,`password`)

);

此时以name和password作为唯一性的整体，即两个记录的name和password都相同时，才违背唯一性。

删除唯一性约束：

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

删除唯一性约束只能通过删除唯一索引的方式删除。

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

如果创建唯一索引时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和组合中第一个列的列名相同。也可以自定义约束名。

删除使用ALTER 表名 DROP INDEX 索引名的方式

例：

ALTER TABLE test_2

DROP INDEX last_name;

ALTER TABLE test_2

DROP INDEX sal;

关于自定义约束名：

在UNIQUE前加上CONSTRAINT 约束名，即可。

例：

CREATE TABLE test_4(

email VARCHAR(25) CONSTRAINT ema UNIQUE,-- 列级约束

phone_num VARCHAR(15),

CONSTRAINT pho_num UNIQUE(phone_num)-- 表级约束

);

主键约束：

主键约束用来唯一标识表中的一行记录。

关键字：PRIMARY KEY

特点：

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

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

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

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

MySQL的主键名是PRIMARY，无法自定义主键约束名。

当创建主键约束时，系统默认会在所在的列会列的组合上建立对应的主键索引（能够通过主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束的对应的索引就自动删除了。

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

添加主键约束

建表时指定主键约束：

例：

CREATE TABLE test_pri(

id INT PRIMARY KEY,-- 列级约束

last_name VARCHAR(15),

salary DECIMAL(10,2),

email VARCHAR(25)

);

CREATE TABLE test_pri_1(

id INT,

last_name VARCHAR(15),

salary DECIMAL(10,2),

email VARCHAR(25),

PRIMARY KEY(id,email)-- 表级约束

);

修改表时添加约束：

ALTER TABLE test_pri_2

ADD PRIMARY KEY(id);

删除主键约束：（最好不要删除表中的主键）

ALTER TABLE test_pri_2

DROP PRIMARY KEY;

自增列：AUTO_INCREMENT

作用：某个字段的值自增

关键字：AUTO_INCREMENT

特点：

一个表最多只能有一个自增长列。

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

子增长列约束的列必须是键列（主键列，唯一键列）

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

如果自增列指定了0或NULL，不会赋值为0或NULL，而是会在当前最大值的基础上自增；如果自增列手动指定了具体值，则直接赋值为具体值。且自增列会自动由小到大排序。

在创建表时加上AUTO_INCREMENT：

CREATE TABLE test_pri_3(

id INT PRIMARY KEY AUTO_INCREMENT,

last_name VARCHAR(15),

salary DECIMAL(10,2),

email VARCHAR(25)

);

在修改表时加上AUTO_INCREMENT：

ALTER TABLE test_pri_4

MODIFY id INT AUTO_INCREMENT;

删除AUTO_INCREMENT：

ALTER TABLE test_pri_4

MODIFY id INT;  -- 不写AUTOINCREMENT即可

注：MySQL8.0后将主键的计数器持久化到重做日志中。即，删除了自增列的某个记录（若此自增列的值为a），即使重启了数据库，下一次添加记录时，新的自增列的值还是a + 1。

外键约束：

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

关键字：FOREIGN KEY

关于主表和从表（父表和子表）：

主表（父表）：被引用的表

从表（子表）：引用其他表的表

特点：

从表的外键列，必须引用/参考主表的主键或唯一约束的列。因为被引用/参考的值必须是唯一的。

在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名。

创建表时就指定外键约束的话，先创建主表，再创建从表。

删表时，先删从表（或先删除外键约束），再删除主表。

当主表的记录被从表引用时，主表的记录将不允许被删除，如果要删除，需要先删除从表中引用该记录的数据，才可以删除主表的数据。

在从表中指定外键约束，并且一个表可以建立多个外键约束。

从表的外键列与主表的被引用的列的名字可以不同，但是数据类型必须一样，逻辑意义一致。

当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键约束名。（使得根据外键的效率很高）

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

在创建表时添加外键

先创建主表：

CREATE TABLE test_dept(

dept_id INT PRIMARY KEY,

dept_name VARCHAR(15)

);

再创建从表：

CREATE TABLE test_emp(

emp_id INT PRIMARY KEY AUTO_INCREMENT,

emp_name VARCHAR(15),

dept_id INT,

CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES test_dept(dept_id)

);

建立外键约束使用CONSTRAINT 外键约束名 FOREIGN KEY(从表的外键列) REFERENCES 主表(主表的被引用的列)

修改表时添加外键：

ALTER TABLE test_emp

ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES test_dept(dept_id);

即在ADD语句后添加CONSTRAINT 外键约束名 FOREIGN KEY(从表的外键列) REFERENCES 主表(主表的被引用的列)

约束等级：

CASCADE方式：在父表上更新/删除记录时，同步更新/删除掉的子表的记录。

SET NULL方式：在父表上更新/删除记录时，将子表上匹配记录的列设为NULL，但是要注意子表的外键列不能为非空约束。

NO ACTION方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行更新/删除。

RESTRICT方式：同No action方式，都是立即检查外键约束。

SET DEFAULT方式：父表有变时，子表将外键里了设置成一个默认的值，但INNODB不能识别。

如果没有指定等级，就相当于RESTRICT方式。

指定等级在CONSTRAINT 外键约束名 FOREIGN KEY(从表的外键列) REFERENCES 主表(主表的被引用的列)

的语句后加上ON UPDATE 约束等级 ON DELETE 约束等级即可。

例：

CREATE TABLE test_emp(

emp_id INT PRIMARY KEY AUTO_INCREMENT,

emp_name VARCHAR(15),

dept_id INT,

CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES test_dept(dept_id) ON UPDATE CASCADE ON DELETE SET NULL

);

对于外键约束，最好使用ON UPDATE CASCADE ON DELETE RESTRICT的方式。

删除外键约束：

第一步可以使用以下操作来查看某个表的外键约束的外键约束名，再删除外键约束：

SELECT * FROM information_schema.table_constraints

WHERE table_name = '表名';

删除：

第二步查看索引名，再删除索引。（只能手动删除）

SHOW INDEX FROM 表名;

ALTER TABLE 表名

DROP INDEX 索引名;-- 索引名与外键约束名一致。

检查约束：

作用：检查某个字段的值是否符合一定要求，一般指的是值的范围。（MySQL8.0及以上支持，在5.7中不起作用）

关键字：CHECK

建表时使用：

在字段后加上CHECK(条件)

当添加记录时，相应的字段的值需要满足CHECK括号后面的条件才能添加成功，否则报错，添加失败。

例：若员工工资不得低于2000

CREATE TABLE test_check(

id INT,

last_name VARCHAR(15),

salary DECIMAL(10,2) CHECK(salary > 2000)

);

默认值约束：

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，就赋值为默认值。

关键字：DEFAULT

建表时添加默认值：

在字段后加上DEFAULT 默认值

例：

CREATE TABLE test_check(

id INT,

last_name VARCHAR(15),

salary DECIMAL(10,2) DEFAULT 2000

);

在修改表时添加约束：

ALTER TABLE 表名

MODIFY 字段名字段类型 DEFAULT 'unknow';

例：

ALTER TABLE test_default

MODIFY last_name VARCHAR(15) DEFAULT 'unknow';

删除默认值约束：

在修改表时的MODIFY语句中不写DEFAULT即可。

MySQL数据库，约束