【MySQL-创建外键约束】

# constraint <外键名> foreign key 字段名 references <主表名> 主键列
create database mydb1 charset=utf8;
use mydb1;
# 创建部门表
create table if not exists department(
	dept_no varchar(20) primary key,
	name varchar(20)
);
# 创建员工表
create table if not exists employee(
	id varchar(20) primary key,
	name varchar(20),
	age int,
	dept_id varchar(20),
	constraint emp_fk foreign key(dept_id) references department(dept_no)
);

# 在外键约束下操作数据
# 1.添加主表数据
# 必须先给主表添加数据
insert into department value('1001','产品部门');
insert into department value('1002','设计部门');
insert into department value('1003','研发部门');
insert into department value('1004','测试部门');
insert into department value('1005','运维部门');

# 2.添加从表数据
# 给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
insert into employee values('1','乔峰','20','1001');
insert into employee values('2','段誉','21','1001');
insert into employee values('3','虚竹','23','1001');
insert into employee values('4','阿紫','18','1002');
insert into employee values('5','扫地僧','35','1002');
insert into employee values('6','李秋水','33','1003');
insert into employee values('7','鸠摩智','50','1003');
# insert into employee values('8','天山童姥','60','1006');必须依赖主键id