11. 约束

2023-10-04 08:54:34

一、约束(constraint)概述

1.1、为什么需要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

1.2、什么是约束

约束是表级的强制规定。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

1.3、约束的分类

根据约束数据列的限制，约束可分为：
- 单列约束：每个约束只约束一列
- 多列约束：每个约束可约束多列数据
根据约束的作用范围，约束可分为：
- 列级约束：只能作用在一个列上，跟在列的定义后面
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义

	位置	支持的约束类型	是否可以起约束名
列级约束	类的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其它支持	可以（主键没有效果）

根据约束起的作用，约束可分为：
- NOT NULL 非空约束，规定某个字段不能为空
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- CHECK 检查约束
- DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

查看某个表已有的约束

-- information_schema数据库名（系统库）
-- table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

example:

USE mysql;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'db';

二、非空约束

作用：限定某个字段/某列的值不允许为空
关键字：NOT NULL
特点：
- 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
- 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符串''不等于NULL，0也不等于NULL

2.1、添加非空约束

2.1.1、建表时添加非空约束

CREATE TABLE 表名称(
    字段名  数据类型 NOT NULL
);

example:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS test01(
	id INT NOT NULL,
	last_name VARCHAR(15) NOT NULL,
	email VARCHAR(25),
	salary DECIMAL(10.2)
);

DESC test01;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test01';

INSERT INTO test01(id,last_name,email,salary)
VALUES(1,'Sakura','sakura@gmail.com',7300);

-- 错误代码： 1048 Column 'last_name' cannot be null
-- insert into test01(id,last_name,email,salary)
-- values(2,null,null,1000);
-- 错误代码： 1048 Column 'id' cannot be null
-- INSERT INTO test01(id,last_name,email,salary)
-- VALUES(null,'Misaka','Misaka@outlook.com',1000);

-- Warning Code : 1364 Field 'last_name' doesn't have a default value
-- insert into test01(id) values(2);

SELECT * FROM test01;

2.1.2、建表后添加非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

example:

SELECT * FROM information_schema.table_constraints WHERE table_name = 'test01';
ALTER TABLE test01 MODIFY email VARCHAR(25) NOT NULL;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test01';

2.3、删除非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;

example:

SELECT * FROM information_schema.table_constraints WHERE table_name = 'test01';
ALTER TABLE test01 MODIFY email VARCHAR(25) NULL;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test01';

三、唯一性约束

作用：用来限制某个字段/某列的值不能重复。
关键字：UNIQUE
特点：
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
- 唯一性约束允许列值为空。
- 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
- MySQL会给唯一约束的列上默认创建一个唯一索引。

3.1、添加唯一约束

3.1.1、建表时添加唯一约束

create table 表名称(
    字段名  数据类型  UNIQUE [KEY]
);

create table 表名称(
	字段名  数据类型,
    [CONSTRAINT 约束名] UNIQUE KEY(字段名)
);

example:

USE test;

CREATE TABLE test02(
    -- 列级约束
	id INT UNIQUE,	
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2),
	CONSTRAINT uk_test02_email UNIQUE(email)
);

DESC test02;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test02';

INSERT INTO test02(id,last_name,email,salary)
VALUES(1,'Sakura','sakura@gmail.com',7700);

-- 错误代码： 1062 Duplicate entry '1' for key 'test02.id'
-- INSERT INTO test02(id,last_name,email,salary)
-- VALUES(1,'Sakura','sakura@gmail.com',7700);

-- 可以向声明为UNIQUE的字段上添加NULL值，而且可以多次添加NULL值
INSERT INTO test02(id,last_name,email,salary)
VALUES(2,'Sakura',NULL,7300);
INSERT INTO test02(id,last_name,email,salary)
VALUES(3,'Sakura',NULL,7100);

SELECT * FROM test02;

3.1.2、建表后指定唯一键约束

ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表); 
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

SELECT * FROM information_schema.table_constraints WHERE table_name = 'test02';
ALTER TABLE test02 ADD CONSTRAINT uk_test02_sal UNIQUE(salary);
ALTER TABLE test02 MODIFY last_name VARCHAR(15) UNIQUE;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test02';

3.1.3、关于复合唯一约束

-- 字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
CREATE TABLE 表名称(
    字段名  数据类型,  
    字段名  数据类型,
    UNIQUE KEY(字段列表) 
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test03(
	id INT,
	NAME VARCHAR(15),
	PASSWORD VARCHAR(25),
	CONSTRAINT uk_user_name_pwd UNIQUE(NAME,PASSWORD)
);

INSERT INTO test03 VALUES(1,'Sakura','27185');
INSERT INTO test03 VALUES(2,'Sakura','Sakura');

-- 错误代码： 1062 Duplicate entry 'Sakura-27185' for key 'test03.uk_user_name_pwd'
-- INSERT INTO test03 VALUES(3,'Sakura','27185');

SELECT * FROM test03;

3.2、删除唯一约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
可以通过 SHOW INDEX FROM 表名称; 查看表的索引

ALTER TABLE 表名  DROP INDEX 唯一索引名;

example:

SELECT * FROM information_schema.table_constraints WHERE table_name = 'test02'; 
ALTER TABLE test02 DROP INDEX last_name;
ALTER TABLE test02 DROP INDEX uk_test02_sal;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test02';

四、PRIMARY KEY 约束

作用：用来唯一标识表中的一行记录。
关键字：primary key
特点：
- 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

4.1、添加主键约束

4.1.1、建表时指定主键约束

CREATE TABLE  表名称(
	字段名  数据类型  PRIMARY KEY
);

CREATE TABLE 表名称(
	字段名  数据类型,
    [CONSTRAINT 约束名] PRIMARY KEY(字段名)
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test04(
	id INT PRIMARY KEY,
	-- 错误代码： 1068 Multiple primary key defined
	-- last_name varchar(15) primary key,
	last_name VARCHAR(15),
	salary DECIMAL(10,2),
	email VARCHAR(25)
);

CREATE TABLE IF NOT EXISTS test05(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10,2),
	email VARCHAR(25),
	-- 没有必要起名字
	CONSTRAINT pk_test05_id PRIMARY KEY(id)	
);

DESC test04;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test04'; 
DESC test05;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test05'; 

INSERT INTO test04(id,last_name,salary,email) 
VALUES(1,'Sakura',3700,'sakura@126.com');

-- 错误代码： 1062 Duplicate entry '1' for key 'test04.PRIMARY'
-- INSERT INTO test04(id,last_name,salary,email) 
-- VALUES(1,'Sakura',3700,'sakura@126.com');
-- 错误代码： 1048 Column 'id' cannot be null
-- INSERT INTO test04(id,last_name,salary,email) 
-- VALUES(null,'Sakura',3700,'sakura@126.com');

SELECT * FROM test04;

4.1.2、建表后增加主键约束

-- 字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

example:

USE test;

CREATE TABLE IF NOT EXISTS test06(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10,2),
	email VARCHAR(25)
);

DESC test06;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test06'; 

ALTER TABLE test06 ADD PRIMARY KEY (id);
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test06';

4.1.3、关于复合主键

-- 表示字段1和字段2的组合是唯一的，也可以有更多个字段
CREATE TABLE 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    PRIMARY KEY(字段名1,字段名2)  
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test07(
	id INT,
	NAME VARCHAR(15),
	PASSWORD VARCHAR(25),
	PRIMARY KEY(NAME,PASSWORD)
);

DESC test07;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test07'; 

INSERT INTO test07 VALUES(1,'Sakura','123abc');
INSERT INTO test07 VALUES(2,'Mikoto','123abc');
-- 错误代码： 1048 Column 'password' cannot be null
-- 多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
-- insert into test07 values(3,'Nanoha',null);

SELECT * FROM test07;

4.2、删除主键约束

ALTER TABLE 表名称 DROP PRIMARY KEY;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

example:

SELECT * FROM information_schema.table_constraints WHERE table_name = 'test06'; 
ALTER TABLE test06 DROP PRIMARY KEY;
DESC test06;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test06';

五、自增列：AUTO_INCREMENT

作用：某个字段的值自增
关键字：AUTO_INCREMENT
特点和要求
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时，可设置自增长
- 自增长列约束的列必须是键列（主键列，唯一键列）
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

5.1、如何指定自增约束

5.1.1、建表时指定自增约束

CREATE TABLE 表名称(
	字段名  数据类型  PRIMARY KEY AUTO_INCREMENT,
);

CREATE TABLE 表名称(
	字段名  数据类型 default 默认值 ,
    字段名  数据类型 unique key AUTO_INCREMENT,  
    字段名  数据类型 not null default 默认值,,
    PRIMARY KEY(字段名)
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test07(
	-- 错误代码： 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key
	-- id int auto_increment,
	id INT PRIMARY KEY AUTO_INCREMENT,
	-- 错误代码： 1063 Incorrect column specifier for column 'last_name'
	-- last_name varchar(15) unique auto_increment
	last_name VARCHAR(15)
);

INSERT INTO test07(last_name) VALUES('Sakura');
INSERT INTO test07(last_name) VALUES('Kagome');
INSERT INTO test07(last_name) VALUES('Kikyo');

-- 当我们向主键（含AUTO_INCREMENT）的字段上添加0或NULL时，实际上会自动的往上添加指定的字段的数值
INSERT INTO test07(id,last_name) VALUES(0,'Akame');
INSERT INTO test07(id,last_name) VALUES(NULL,'Kurome');

INSERT INTO test07(id,last_name) VALUES(10,'Rimuru');
INSERT INTO test07(id,last_name) VALUES(-10,'Milim');

INSERT INTO test07(last_name) VALUES('Fate');

SELECT * FROM test07;

5.1.2、建表后指定自增约束

alter table 表名称 modify 字段名 数据类型 auto_increment;

example:

USE test;

CREATE TABLE IF NOT EXISTS test08(
	id INT PRIMARY KEY,
	last_name VARCHAR(15)
);

DESC test08;
ALTER TABLE test08 MODIFY id INT AUTO_INCREMENT;
DESC test08;

5.2、如何删除自增约束

alter table 表名称 modify 字段名 数据类型;

example:

DESC test08;
ALTER TABLE test08 MODIFY id INT;
DESC test08;

5.6、MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

USE test;

CREATE TABLE IF NOT EXISTS test09(
	id INT PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test09() VALUES(0),(0),(0),(0);

SELECT * FROM test09;

DELETE FROM test09 WHERE id = 4;

INSERT INTO test09 VALUES(0);

SELECT * FROM test09;

DELETE FROM test09 WHERE id = 5;

重启数据库，以管理员身份运行cmd，先输入net stop MySQL服务名，在输入net start MySQL服务名，重新插入一个空值。

SELECT * FROM test09;

-- MySQL8.0之前版本结果为4，MySQL8.0之后的版本结果为6
INSERT INTO test09 VALUES(0);

SELECT * FROM test09;

在MySQL 5.7系统中，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

六、FOREIGN KEY 约束

作用：
- 限定某个表的某个字段的引用完整性。
  - 比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。
关键字：FOREIGN KEY
主表和从表/父表和子表
- 主表（父表）：被引用的表，被参考的表
- 从表（子表）：引用别人的表，参考别人的表
  - 例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
  - 例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。
特点：
- 从表的外键列，必须引用/参考主表的主键或唯一约束的列
- 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名。
- 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
- 删表时，先删从表（或先删除外键约束），再删除主表
- 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
- 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
- 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
  - 如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
- 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。
- 删除外键约束后，必须手动删除对应的索引

6.1、添加外键约束

6.1.1、建表时添加外键约束

CREATE TABLE 主表名(
	字段1  数据类型1  PRIMARY KEY
);

CREATE TABLE 从表名(
	字段2  数据类型1,
    [CONSTRAINT 外键约束名称] FOREIGN KEY(字段2) REFERENCES 主表名(字段1)
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test10(
	dept_id INT PRIMARY KEY,
	dept_name VARCHAR(15)
);

CREATE TABLE IF NOT EXISTS test11(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(15),
	department_id INT,
	CONSTRAINT fk_test11_dept_id FOREIGN KEY(department_id) REFERENCES test10(dept_id)
);

DESC test10;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test10'; 
DESC test11;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test11'; 

-- 错误代码： 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`test11`, CONSTRAINT `fk_test11_dept_id` FOREIGN KEY (`department_id`) REFERENCES `test10` (`dept_id`))
-- INSERT INTO test11 values(1001,'Sakura',10);

-- 在主表test10中添加了10号部门以后，可以在从表中添加10号部门的员工
INSERT INTO test10 VALUES(10,'IT');
INSERT INTO test11 VALUES(1001,'Sakura',10);

-- 错误代码： 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`test11`, CONSTRAINT `fk_test11_dept_id` FOREIGN KEY (`department_id`) REFERENCES `test10` (`dept_id`))
-- DELETE FROM test10 WHERE dept_id = 10;
-- 错误代码： 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`test11`, CONSTRAINT `fk_test11_dept_id` FOREIGN KEY (`department_id`) REFERENCES `test10` (`dept_id`))
-- UPDATE test10 SET dept_id = 20 WHERE dept_id = 10;

6.1.2、建表后添加外键约束

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段);

example:

USE test;

CREATE TABLE IF NOT EXISTS test12(
	dept_id INT PRIMARY KEY,
	dept_name VARCHAR(15)
);

CREATE TABLE IF NOT EXISTS test13(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(15),
	department_id INT
);

DESC test13;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test13'; 

ALTER TABLE test13 ADD CONSTRAINT fk_test13_dept_id FOREIGN KEY(department_id) REFERENCES test12(dept_id);

DESC test13;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test13';

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

6.2、约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action，都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

example:

-- on update cascade on delete set null

CREATE TABLE IF NOT EXISTS test14(
	dept_id INT PRIMARY KEY,
	dept_name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS test15(
	emp_id INT PRIMARY KEY,
	emp_name VARCHAR(5),
	department_id INT,
	FOREIGN KEY(department_id) REFERENCES test14(dept_id) ON UPDATE CASCADE ON DELETE SET NULL
);

INSERT INTO test14 VALUES(1001,'教学部');
INSERT INTO test14 VALUES(1002,'财务部');
INSERT INTO test14 VALUES(1003,'咨询部');

INSERT INTO test15 VALUES(1,'张三',1001);
INSERT INTO test15 VALUES(2,'李四',1001);
INSERT INTO test15 VALUES(3,'王五',1002);

SELECT * FROM test14;
SELECT * FROM test15;

UPDATE test14 SET dept_id = 1004 WHERE dept_id = 1002;
SELECT * FROM test14;
SELECT * FROM test15;

DELETE FROM test14 WHERE dept_id = 1004;
SELECT * FROM test14;
SELECT * FROM test15;

6.3、删除外键约束

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

example:

DESC test13;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test13'; 
-- 删除外键约束
ALTER TABLE test13 DROP FOREIGN KEY fk_test13_dept_id;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test13'; 
-- 在手动的删除外键约束对应的普通索引
SHOW INDEX FROM test13;
ALTER TABLE test13 DROP INDEX fk_test13_dept_id;
SHOW INDEX FROM test13;

6.3、开发场景

建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的*数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

七、CHECK 约束

作用：
检查某个字段的值是否符号xx要求，一般指的是值的范围
关键字：CHECK
MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告

USE test;

CREATE TABLE IF NOT EXISTS test16(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10,2) CHECK(salary>2000)
);

INSERT INTO test16 VALUES(1,'Sakura',3700);

-- 错误代码： 3819 Check constraint 'test16_chk_1' is violated.
-- insert into test16 values(1,'Sakrua',1500);

SELECT * FROM test16;

八、DEFAULT约束

作用：
- 给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。
关键字：DEFAULT
默认值约束一般不在唯一键和主键列上加

8.1、如何给字段加默认值

8.1.1、建表时给字段加默认值

create table 表名称(
    字段名  数据类型 default 默认值
);

example:

USE test;

CREATE TABLE IF NOT EXISTS test17(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10,2) DEFAULT 2000
);

DESC test17;

INSERT INTO test17(id,last_name,salary) VALUES(1,'Sakura',3700);
INSERT INTO test17(id,last_name) VALUES(2,'Mikoto');

SELECT * FROM test17;

8.1.2、建表后添加默认值

alter table 表名称 modify 字段名 数据类型 default 默认值;

example:

USE test;

CREATE TABLE IF NOT EXISTS test18(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10,2)
);

DESC test18;
ALTER TABLE test18 MODIFY salary DECIMAL(10,2) DEFAULT 2500;
DESC test18;

8.2、如何删除默认值约束

alter table 表名称 modify 字段名 数据类型;

example:

DESC test18;
ALTER TABLE test18 MODIFY salary DECIMAL(10,2);
DESC test18;

九、练习题

-- 1. 已经存在数据库test04_emp，两张表emp2和dept2
CREATE DATABASE test04_emp; 
USE test04_emp; 

CREATE TABLE emp2( 
	id INT, 
	emp_name VARCHAR(15) 
);

CREATE TABLE dept2( 
	id INT, 
	dept_name VARCHAR(15) 
);

-- 2. 向表emp2的id列中添加PRIMARY KEY约束 
ALTER TABLE emp2 ADD PRIMARY KEY(id);

-- 3. 向表dept2的id列中添加PRIMARY KEY约束 
ALTER TABLE dept2 ADD PRIMARY KEY(id);

-- 4. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES dept2(id);

-- 5、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library;
USE test01_library;

-- 6、创建表 books，表结构如下：
/*
	字段名	字段说明	数据类型
	id	书编号 		INT 
	name 	书名 		VARCHAR(50) 
	authors 作者 		VARCHAR(100) 
	price 	价格 		FLOAT 
	pubdate 出版日期 	YEAR 
	note 	说明 		VARCHAR(100) 
	num 	库存 		INT
*/
CREATE TABLE IF NOT EXISTS books (
	id INT,
	NAME VARCHAR(50),
	AUTHORS VARCHAR(100),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

-- 7、使用ALTER语句给books按如下要求增加相应的约束
/*
	字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
	id 	书编号 		INT(11) 	是 	否 	是 	是 	是 
	name 	书名 		VARCHAR(50) 	否 	否 	是 	否 	否 
	authors 作者 		VARCHAR(100) 	否 	否 	是 	否 	否 
	price 	价格 		FLOAT 		否 	否 	是 	否 	否 
	pubdate 出版日期 	YEAR 		否 	否 	是 	否 	否 
	note 	说明 		VARCHAR(100) 	否 	否 	否 	否 	否 
	num 	库存 		INT(11) 	否 	否 	是 	否	否
*/
ALTER TABLE books MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE books MODIFY NAME VARCHAR(50) NOT NULL;
ALTER TABLE books MODIFY AUTHORS VARCHAR(100) NOT NULL;
ALTER TABLE books MODIFY price FLOAT NOT NULL;
ALTER TABLE books MODIFY pubdate YEAR NOT NULL;
ALTER TABLE books MODIFY num INT NOT NULL;

-- 8. 创建数据库test04_company 
CREATE DATABASE IF NOT EXISTS test04_company;
USE test04_company;

-- 9. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees
-- offices表：
/*
	字段名		数据类型	主键	外键	非空	唯一	自增
	officeCode	INT(10)		是	否	是	是	否
	city		VARCHAR(50)	否	否	是	否	否
	address		VARCHAR(50)	否	否	否	否	否
	country		VARCHAR(50)	否	否	是	否	否
	postalCode	VARCHAR(15)	否	否	否	是	否
*/
CREATE TABLE IF NOT EXISTS offices(
	officeCode INT(10) PRIMARY KEY,
	city VARCHAR(50) NOT NULL,
	address VARCHAR(50),
	country VARCHAR(50) NOT NULL,
	postalCode VARCHAR(15),
	CONSTRAINT uk_off_poscode UNIQUE(postalCode)
);
-- employees表：
/*
字段名		数据类型	主键	外键	非空	唯一	自增
employeeNumber	INT(11)		是	否	是	是	是
lastName	VARCHAR(50)	否	否	是	否	否
firstName	VARCHAR(50)	否	否	是	否	否
mobile		VARCHAR(25)	否	否	否	是	否
officeCode	INT(10)		否	是	是	否	否
jobTitle	VARCHAR(50)	否	否	是	否	否
birth		DATETIME	否	否	是	否	否
note		VARCHAR(255)	否	否	否	否	否
sex		VARCHAR(5)	否	否	否	否	否
*/
CREATE TABLE IF NOT EXISTS employees(
	emoployeeNumber INT(11) PRIMARY KEY AUTO_INCREMENT,
	lastName VARCHAR(50) NOT NULL,
	firstName VARCHAR(50) NOT NULL,
	mobile VARCHAR(25) UNIQUE,
	officeCode INT(10) NOT NULL,
	jobTitle VARCHAR(50) NOT NULL,
	birth DATETIME NOT NULL,
	note VARCHAR(255),
	sex VARCHAR(5),
	CONSTRAINT fk_emp_offcode FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);

-- 10. 将表employees的mobile字段修改到officeCode字段后面 
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;

-- 11. 将表employees的birth字段改名为employee_birth 
ALTER TABLE employees CHANGE birth employee_birth DATETIME;

-- 12. 修改sex字段，数据类型为CHAR(1)，非空约束 
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;

-- 13. 删除字段note 
ALTER TABLE employees DROP COLUMN note;

-- 14. 增加字段名favoriate_activity，数据类型为VARCHAR(100) 
ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

-- 15. 将表employees名称修改为employees_info
RENAME TABLE employees TO employees_info;

码农公寓

一、约束(constraint)概述

1.1、为什么需要约束

1.2、什么是约束

1.3、约束的分类

二、非空约束

2.1、添加非空约束

2.1.1、建表时添加非空约束

2.1.2、建表后添加非空约束

2.3、删除非空约束

三、唯一性约束

3.1、添加唯一约束

3.1.1、建表时添加唯一约束

3.1.2、建表后指定唯一键约束

3.1.3、关于复合唯一约束

3.2、删除唯一约束

四、PRIMARY KEY 约束

4.1、添加主键约束

4.1.1、建表时指定主键约束

4.1.2、建表后增加主键约束

4.1.3、关于复合主键

4.2、删除主键约束

五、自增列：AUTO_INCREMENT

5.1、如何指定自增约束

5.1.1、建表时指定自增约束

5.1.2、建表后指定自增约束

5.2、如何删除自增约束

5.6、MySQL 8.0新特性—自增变量的持久化

六、FOREIGN KEY 约束

6.1、添加外键约束

6.1.1、建表时添加外键约束

6.1.2、建表后添加外键约束

6.2、约束等级

6.3、删除外键约束

6.3、开发场景

七、CHECK 约束

八、DEFAULT约束

8.1、如何给字段加默认值

8.1.1、建表时给字段加默认值

8.1.2、建表后添加默认值

8.2、如何删除默认值约束

九、练习题

相关文章