Table~MySQL多表关系-外键约束

一、外键约束

1.多表关系

MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系，多对多

2.介绍

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

3.特点

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

• 主表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同

二、创建外键约束

1.方式1-在创建表时设置外键约束

在 create table 语句中，通过 foreign key 关键字来指定外键

• 格式：

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

• 实例：

  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) -- 外键约束
  );
  

2.方式2-在修改表时设置外键约束

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

• 格式：

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

• 实例：

  -- 创建部门表
  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_pk foreign key (dept_id) references dept2(deptno);
  

三、在外键约束下的数据操作

1.数据插入

-- 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');  -- 不可以

2.删除数据

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

3.删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系

• 格式：

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

• 实例：

  alter table emp2 drop foreign key dept_id_pk;
  

四、多对多关系

1.介绍

在多对多关系中，A表的一行对应B的多行，B表的一行对应A表的多行，我们要新增加一个中间表，来建立多对多关系。

2.实例

-- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
);
-- 2 创建课程表course(右侧主表)
    create table course(
    cid  int primary key auto_increment,
    cidname varchar(20)
);
-- 3创建中间表student_course/score(从表)
create table score(
    sid int,
    cid int,
    score double
);
-- 4建立外键约束(2次)
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 6给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 7给中间表添加数据
insert into score values(1,1,80),(1,2,67),(2,1,78),(2,3,99),(3,2,45),(3,3,66);