2024Mysql And Redis基础与进阶操作系列（4）作者——LJS[含MySQL FOREIGN KEY、CHECK 、DEFAULT 约束举例说明等详解步骤及常见报错问题所对应的解决方法]

接上集

1. FOREIGN KEY 约束

1.1 作用

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

例如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

1.2 关键字

FOREIGN KEY

1.3 主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是 主表，选课表是从表。

1.4 特点

FOREIGN KEY 约束特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列

为什么？因为被依赖/被参考的值必须是唯一的

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

例如 student_ibfk_1，也可以指定外键约束名。

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

当主表的记录被从表参照时，主表的记录将不允许删除.

如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

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

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

如果类 型不一样，创建子表时，就会出现错误

“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。

比如：都是表示部门编号，都是int类型。

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

根据外键查询效率很高

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

1.5 添加外键约束

(1)建表时  

create table 主表名称(     

    字段1  数据类型  primary key,         
    字段2  数据类型 ); 


create table 从表名称(     
    字段1  数据类型  primary key,     
    字段2  数据类型,     
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) ); 

//(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样 

//(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

举例

create table dept( 
    //主表     
    
    did int primary key,        
    
    //部门编号         
    
    dname varchar(50)           
    
    //部门名称 );create table emp(

    //从表     
    
    eid int primary key,  
    
    //员工编号     
    
    ename varchar(5),    
    
    //员工姓名     
    
    deptid int,           
    
    //员工所在的部门     
    
    foreign key (deptid) references dept(did)); 
    
    //在从表中指定外键约束         
    
    //emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号

说明：

(1)主表dept必须先创建成功，然后才能创建emp表，指定外键成功。

(2)删除表时，先删除从表emp，再删除主表dept

建表后一般情况下，表与表的关联都是提前设计好了的，因此会在创建表的时候就把外键约束定义好。

不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

格式

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

示例

create table dept(     
    
    did int primary key,        
    
    //部门编号         
    
    dname varchar(50));           
    
    //部门名称


create table emp( 
       
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
   
    deptid int            //员工所在的部门 
); 

//这两个表创建时，没有指定外键的话，那么创建顺序是随意 

alter table emp add foreign key (deptid) references dept(did);

1.6 演示过程所产生问题及解决方法、

(1)失败：不是键列

create table dept( 
   
    did int ,       //部门编号 
   
    dname varchar(50)           //部门名称 
); 


create table emp( 
   
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
   
    deptid int,           //员工所在的部门 
        
    foreign key (deptid) references dept(did) 
); 


//ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列

(2)失败：数据类型不一致

create table dept( 
   
    did int primary key,        //部门编号    
   
    dname varchar(50)           //部门名称 
); 


create table emp( 
   
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
   
    deptid char,          //员工所在的部门 
    
    foreign key (deptid) references dept(did) 
); 

//ERROR 1215 (HY000): Cannot add foreign key constraint

原因:

从表的deptid字段和主表的did字 段的数据类型不一致，并且要它俩的逻辑意义一致 
 

(3)成功，两个表字段名一样

create table dept( 
   
    did int primary key,        //部门编号    
   
    dname varchar(50)           //部门名称 
); 


create table emp( 
   
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
   
    did int,              //员工所在的部门 
    
    foreign key (did) references dept(did)   
    
    //emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号    
    
    //是否重名没问题，因为两个did在不同的表中 
);

(4)添加、删除、修改问题

create table dept( 
   did int primary key,        //部门编号    
   dname varchar(50)           //部门名称 
); 


create table emp( 
   eid int primary key,  //员工编号 
   ename varchar(5),     //员工姓名 
   deptid int,           //员工所在的部门 
   foreign key (deptid) references dept(did));  
    
//emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号 


insert into dept values(1001,'教学部'); insert into dept values(1003, '财务部'); 

insert into emp values(1,'张三',1001); 

//添加从表记录成功，在添加这条记录时，要求部门表有
1001部门 

insert into emp values(2,'李四',1005);

//添加从表记录失败 

ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row: a foreign key 
 constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY 
(`deptid`)  REFERENCES `dept` (`did`)) 

//从表emp添加记录失败，因为主表dept没有1005部门 


mysql> select * from dept; 
+------+--------+ 
| did  | dname  | 
+------+--------+ 
| 1001 | 教学部  | 
| 1003 | 财务部  | 
+------+--------+ 
2 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid | 
+-----+-------+--------+ 
|   1 | 张三   |   1001 | 
+-----+-------+--------+ 
1 row in set (0.00 sec)update emp set deptid = 1002 where eid = 1;

//修改从表失败  

ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row（子表的记录）: a 
 foreign key constraint fails（外键约束失败） (`atguigudb`.`emp`, CONSTRAINT 
`emp_ibfk_1`  FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))  

//部门表did字段现在没有1002的值，所以员工 表中不能修改员工所在部门deptid为1002 

update dept set did = 1002 where did = 1001;

//修改主表失败 

ERROR 1451 (23000): Cannot delete（删除） or update（修改） a parent row（父表的记
录）: a  foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` 
FOREIGN KEY  (`deptid`) REFERENCES `dept` (`did`)) 

//部门表did的1001字段已经被emp引用了,所以部门表的1001字段就不能修改了。 

update dept set did = 1002 where did = 1003;

//修改主表成功 因为部门表的1003部门没有被emp表引用，所以可以修改 

delete from dept where did=1001; 

//删除主表失败 

ERROR 1451 (23000): Cannot delete（删除） or update（修改） a parent row（父表记录）: 
a  foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY  (`deptid`) REFERENCES `dept` (`did`))  

//因为部门表did的1001字段已经被emp引用了，所以部门表的 1001字段对应的记录就不能被删除

总结：

FOREIGN KEY 约束总结

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

1.7 约束等级

约束等级

Cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录Set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not nullNo action方式如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作Restrict方式 同no action， 都是立即检查外键约束 Set default方式（在可视化工具SQLyog中可能显示空白）父表有变更时，子表将外键列设置成 一个默认的值，但Innodb不能识别

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

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

举例1：

create table dept( 
   
    did int primary key,        //部门编号    
   
    dname varchar(50)           //部门名称 
); 


create table emp( 
   
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
    
    eptid int,           #员工所在的部门 
    
    foreign key (deptid) references dept(did)  on update cascade on delete set 
null);     
    //把修改操作设置为级联修改等级，把删除操作设置为set null等级 

insert into dept values(1001,'教学部'); 

insert into dept values(1002, '财务部'); 

insert into dept values(1003, '咨询部'); 

insert into emp values(1,'张三',1001); //在添加这条记录时，要求部门表有1001部门 

insert into emp values(2,'李四',1001); 

insert into emp values(3,'王五',1002);

mysql> select * from dept; 

mysql> select * from emp;

修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004
了  

mysql> update dept set did = 1004 where did = 1002; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 


mysql> select * from dept; 
+------+--------+ 
| did  | dname  | 
+------+--------+ 
| 1001 | 教学部 | 
| 1003 | 咨询部 | 
| 1004 | 财务部 | //原来是1002，修改为1004 
+------+--------+ 
3 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid | 
+-----+-------+--------+ 
|   1 | 张三  |   1001 | 
|   2 | 李四  |   1001 | 
|   3 | 王五  |   1004 | //原来是1002，跟着修改为1004 
+-----+-------+--------+ 
3 rows in set (0.00 sec)

删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了

mysql> delete from dept where did=1001; 
Query OK, 1 row affected (0.00 sec) 


mysql> select * from dept; 
+------+--------+ 
| did  | dname  | //部门表中1001部门被删除 
+------+--------+ 
| 1003 | 咨询部 | 
| 1004 | 财务部 | 
+------+--------+ 
2 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid |  //原来1001部门的员工也被删除了 
+-----+-------+--------+ 
|   3 | 王五  |   NULL | 
+-----+-------+--------+ 
1 row in set (0.00 sec)

举例2：

create table dept(    

    did int primary key,        //部门编号    
    dname varchar(50)           //部门名称 
); 


create table emp(
   
    eid int primary key,  //员工编号 
   
    ename varchar(5),     //员工姓名 
   
    deptid int,           //员工所在的部门 
       
    foreign key (deptid) references dept(did)  on update cascade on delete 
cascade    //把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级 
); 

insert into dept values(1002, '财务部'); 

insert into dept values(1003, '咨询部'); 

insert into emp values(1,'张三',1001); //在添加这条记录时，要求部门表有1001部门 

insert into emp values(2,'李四',1001); 

insert into emp values(3,'王五',1002); 



mysql> select * from dept; 
+------+--------+ 
| did  | dname  | 
+------+--------+ 
| 1001 | 教学部 | 
| 1002 | 财务部 | 
| 1003 | 咨询部 | 
+------+--------+ 
3 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid | 
+-----+-------+--------+ 
|   1 | 张三  |   1001 | 
|   2 | 李四  |   1001 | 
|   3 | 王五  |   1002 | 
+-----+-------+--------+ 
3 rows in set (0.00 sec)

修改主表，从表对应的字段自动修改

mysql> update dept set did = 1004 where did = 1002; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 

mysql> select * from dept; 
+------+--------+ 
| did  | dname  | 
+------+--------+ 
| 1001 | 教学部 | 
| 1003 | 咨询部 | 
| 1004 | 财务部 | //部门1002修改为1004 
+------+--------+ 
3 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid | 
+-----+-------+--------+ 
|   1 | 张三  |   1001 | 
|   2 | 李四  |   1001 | 
|   3 | 王五  |   1004 | //级联修改 
+-----+-------+--------+ 
3 rows in set (0.00 sec)

删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了

mysql> delete from dept where did=1001; 
Query OK, 1 row affected (0.00 sec) 


mysql> select * from dept; 
+------+--------+ 
| did  | dname  | //1001部门被删除了 
+------+--------+ 
| 1003 | 咨询部 | 
| 1004 | 财务部 |  
+------+--------+ 
2 rows in set (0.00 sec) 


mysql> select * from emp; 
+-----+-------+--------+ 
| eid | ename | deptid |  //1001部门的员工也被删除了 
+-----+-------+--------+ 
|   3 | 王五  |   1004 | 
+-----+-------+--------+ 
1 row in set (0.00 sec)

1.8 删除外键约束

步骤如下：

(1)第一步先查看约束名和删除外键约束

//查看某个表的约束名 

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

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

(2)第二步查看索引名和删除索引。（注意:只能手动删除）

//查看某个表的索引名 SHOW INDEX FROM 表名称; ALTER TABLE 从表名 DROP INDEX 索引名;

示例

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 
'emp'; 

mysql> alter table emp drop foreign key emp_ibfk_1; 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0  Warnings: 0

mysql> show index from emp; 

mysql> alter table emp drop index deptid; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0  Warnings: 0 

mysql>  show index from emp;

2面试题补充

问题1：

• 如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否 一定要建外键约束？

答案

• 答：不是的

问题2：建和不建外键约束有什么区别？

答案：

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

问题3：那么建和不建外键约束和查询有没有关系？

答案：没有

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

3. CHECK 约束

3.1 作用 检查某个字段的值是否符号某某要求，一般指的是值的范围

3.2 关键字 CHECK

3.3 补充说明：MySQL 5.7 不支持

create table employee( 
   
    eid int primary key, 
   
    ename varchar(5), 
   
    gender char check ('男' or '女') 
); 
 
 
insert into employee values(1,'张三','妖'); 
 
mysql> select * from employee; 
+-----+-------+--------+ 
| eid | ename | gender | 
+-----+-------+--------+ 
|   1 | 张三   | 妖     | 
+-----+-------+--------+ 
1 row in set (0.00 sec)

insert into employee values(1,'张三','妖'); 
 
 
mysql> select * from employee; 
+-----+-------+--------+ 
| eid | ename | gender | 
+-----+-------+--------+ 
|   1 | 张三   | 妖     | 
+-----+-------+--------+ 
1 row in set (0.00 sec)

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’)) 
CHECK(height>=0 AND height<3)

4 DEFAULT 约束

4.1 作用

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

4.2 关键字

• DEFAULT

4.3 如何给字段加默认值

(1)建表时

create table 表名称(     
    
    字段名  数据类型  
    
    primary key,     
    
    字段名  数据类型  
    
    unique key not null,       
    
    字段名  数据类型  unique key,     
    
    字段名  数据类型  not null default 默认值,  ); 
 
 
 
create table 表名称(     
    
    字段名  
 
    数据类型 default 默认值 ,     
 
    字段名  数据类型 not null default 默认值,           
 
    字段名  数据类型 not null default 默认值,     
    
    primary key(字段名),     unique key(字段名) ); 
 

    注意：默认值约束一般不在唯一键和主键列上加

create table employee(     
 
    eid int primary key,     
 
    ename varchar(20) not null,     
 
    gender char default '男',     
 
    tel char(11) not null default '' ); 
 
//默认是空字符串 
 
 
mysql> desc employee; 
+--------+-------------+------+-----+---------+-------+ 
| Field  | Type        | Null | Key | Default | Extra | 
+--------+-------------+------+-----+---------+-------+ 
| eid    | int(11)     | NO   | PRI | NULL    |       | 
| ename  | varchar(20) | NO   |     | NULL    |       | 
| gender | char(1)     | YES  |     | 男      |       | 
| tel    | char(11)    | NO   |     |         |       | 
+--------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)

insert into employee values(1,'汪飞','男','13700102535'); 
 
//成功 
 
 
mysql> select * from employee; 
 
+-----+-------+--------+-------------+ 
| eid | ename | gender | tel         | 
+-----+-------+--------+-------------+ 
|   1 | 汪飞  | 男     | 13700102535 | 
+-----+-------+--------+-------------+ 
1 row in set (0.00 sec) 
 
insert into employee(eid,ename) values(2,'天琪'); 
 
//成功 
 
//ERROR 1062 (23000): Duplicate entry '' for key 'tel'   
 
//如果tel有唯一性约束的话会报错，如果tel没有唯一性约束，可以添加成功

CREATE TABLE myemp( 
   
    id INT AUTO_INCREMENT PRIMARY KEY, 
   
    NAME VARCHAR(15), 
   
    salary DOUBLE(10,2) DEFAULT 2000 
)

(2)建表后

• 如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约
• 束就被删除了 

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

• 同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中
• 保留默认值约束，否则就删除了 

alter table 表名称 modify 字段名 数据类型 default 默认值 not null; 
create table employee( 
   
    eid int primary key, 
   
    ename varchar(20), 
   
    gender char, 
    
    tel char(11) not null 
);

mysql> desc employee; 
 
+--------+-------------+------+-----+---------+-------+ 
| Field  | Type        | Null | Key | Default | Extra | 
+--------+-------------+------+-----+---------+-------+ 
| eid    | int(11)     | NO   | PRI | NULL    |       | 
| ename  | varchar(20) | YES  |     | NULL    |       | 
| gender | char(1)     | YES  |     | NULL    |       | 
| tel    | char(11)    | NO   |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 
 
 
alter table employee modify gender char default '男'; 
 
//给gender字段增加默认值约束 
 
alter table employee modify tel char(11) default ''; 
 
//给tel字段增加默认值约束 

给tel字段增加默认值约束，并保留非空约束

mysql> desc employee; 

+--------+-------------+------+-----+---------+-------+ 
| Field  | Type        | Null | Key | Default | Extra | 
+--------+-------------+------+-----+---------+-------+ 
| eid    | int(11)     | NO   | PRI | NULL    |       | 
| ename  | varchar(20) | YES  |     | NULL    |       | 
| gender | char(1)     | YES  |     | 男      |       | 
| tel    | char(11)    | NO   |     |         |       | 
+--------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)

4.4 如何删除默认值约束

删除默认值约束，也不保留非空约束

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

• 删除默认值约束，保留非空约束

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

• 删除gender字段默认值约束，如果有非空约束，也一并删除

alter table employee modify gender char;

• 删除tel字段默认值约束，保留非空约束

alter table employee modify tel char(11)  not null;

mysql> desc employee; 
 
+--------+-------------+------+-----+---------+-------+ 
| Field  | Type        | Null | Key | Default | Extra | 
+--------+-------------+------+-----+---------+-------+ 
| eid    | int(11)     | NO   | PRI | NULL    |       | 
| ename  | varchar(20) | YES  |     | NULL    |       | 
| gender | char(1)     | YES  |     | NULL    |       | 
| tel    | char(11)    | NO   |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 

5. 面试题补充（2）

面试1.为什么建表时，加 not null default '' 或 default 0

• 答：不让表中出现null值。

面试2.为什么不想要 null 的值

• 答:不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。
• 碰到运算符， 通常返回null。 效率不高。影响提高索引效果。
• 因此，我们往往在建表时 not null default '' 或 default 0

面试3、带 AUTO_INCREMENT约束的字段值是从1开始的吗？

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

......