MySQL的多表操作-外键约束

外键约束

介绍

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

如下图员工表中dept_id 表示员工属于哪个部门，员工表就是从表，部门表就是主表。

特点

定义一个外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。

• 必须为主表定义主键。

• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。

• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。(就比如联合主键)

• 外键中列的数目必须和主表的主键中列的数目相同。

• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

添加外键约束

方式1：在创建表时设置外键约束
基本语法格式：

[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]
1

举例：

create database mydb3;
use mydb3;
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
# 从表
create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20),  -- 员工所属部门
  constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外键约束
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

方式2 在修改表时添加
外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foreign key(<列名>) references 
<主表名> (<列名>);
1
2

-- 创建部门表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

数据插入

• 注意必须先给主表添加数据
• 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
  就比如emp 中的员工所属部门不能随便填必须是dept 表里有的。

-- 1、添加主表数据
 -- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部’);

-- 2、添加从表数据
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003'); 
insert into emp values('8','天山童姥',60, '1005');  
-- 最后一行不可以，因为主表里面没有 1005
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除数据

-- 3、删除数据
 /*
   注意：
       1：主表的数据被从表依赖时，不能删除，否则可以删除
       2: 从表的数据可以随便删除
 */
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除
1
2
3
4
5
6
7
8
9

删除外键约束

alter table <表名> drop foreign key <外键约束名>;

alter table emp2 drop foreign key dept_id_fk;
1
2
3