MySQL学习记录（六）：外键约束

MySQL学习记录（六）：外键约束

外键约束（foreign key ）

• 主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

• 外键（foreign key ）：用于建立表与表之间的某种约束的关系，简单来说，可以理解为子表中的某些值只能填主表中的某些值

• foreign key外键的定义语法

  [CONSTRAINT symbol] FOREIGN KEY（外键字段） 
    
  REFERENCES tbl_name (主表主键)
    
  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    
  

  该语法可以在CREATE TABLE 和 ALTER TABLE 时使用，分为两个部分：定义（创建）外键以及选择级联动作类型

  一、创建外键

  方式一：在建立表时直接建立外键约束

  语句：

  constraint 外键名 foreign key(子表对应字段名) refenences 主表名(主表主键字段名)

  #先创建部门表，id为主键，对应不同的部门
  CREATE TABLE dept (
      id int PRIMARY KEY auto_increment,
      dname VARCHAR(50) not null
  );
  insert into dept values
  (1,"技术部"),
  (2,"销售部"),
  (3,"市场部"),
  (4,"行政部"),
  (5,'财务部'),
  (6,'总裁办公室');
  #在建立表时直接建立外键约束:让员工表的dept_id字段对应部门表中的id字段
    creat table person(
        id int primary key atuo_increment,
        name varchar(32) not null,
        age tinyint unsigned,
        salary decimal(10,2),
        dept_id int,
        #设置dept_fk为外键名字，dept_id为子表中的关系字段，dept为主表对应字段
        constraint dept_fk foreign key(dept_id) references dept(id);
    ); 
  

  • 

  

  方式二：在已经建立的表中添加外键

  语句：

  alter table 子表名 add constraint 外键名 foreign key (子表字段名) references 主表名(主表主键字段名)

  #在已经建立的表中添加外键:
  #修改后已经关联的子表字段再插入数据时，就不允许插入主表主键字段不存在的值了
    alter table person add
    constraint dept_fk
    foreign key (dept_id)
    references dept(id);
  

  

注意：从表的外键字段数据类型与指定的主表主键应该相同。

• 解除外键约束

  语法：通过外键名解除外键约束

  删除外键：alter table 子表名 drop foreign key 外键名称;

  删除外键索引：drop index 外键名on 子表名;

  

  注意：

  1.通过查看子表的创建过程可以查看到外键名称

  语句：show create table person;

  2.对一个字段创建外键的时候，系统会自动会给该外键创建一个普通索引（MUL），索引名字 和创建的外键名字，没有外键名字时系统会自己生成。

  3.删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除。

  语句：drop index 外键名on 子表名;

  二、级联动作

  可以理解为：主表改变时，子表该怎么变化

  语句：

  ​ [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

  ​ [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

  类型	说明
  restrict(默认)	当主表删除记录时，如果从表中有相关联记录则不允许主表删除 当主表更改主键字段值时，如果从表有相关记录则不允许更改
  cascade	数据级联更新：当主表删除记录或更改被参照字段的值时,从表会级联更新
  set null	当主表删除记录时，从表外键字段值变为null ；当主表更改主键字段值时，从表外键字段值变为null

  #cascade,数据级联更新
  alter table person add
  constraint dept_fk
  foreign key(dept_id)
  references dept(id)
  on delete cascade on update cascade;
  #将市场部id改为7
  update dept set id=7 where dname="市场部"
  #查看更改主表主键字段后子表的内容
  select * from person
  

    #set null：当主表删除记录/更改主键字段值时，从表外键字段值变为null 
    alter table person add
    constraint dept_fk
    foreign key(dept_id)
    references dept(id)
    on delete set null on update set null;
    #更改主键id
    update dept set id=8 where dname="市场部"
    select * from person