MySQL 外键(foreign key)

Administrator
发布于 2023-10-05 / 25 阅读 / 0 评论 / 0 点赞

MySQL 外键(foreign key)

一、什么是外键

  1. 外键是某表中的一列是另一张表的主键;反之A表的主键作为B表的某一列;

  2. 外键也是索引的一种,是通过一张表中的一列指向另一张表的主键,来对两张表进行关联;

  3. 一张表可以有一个外键,也可以存在多个外键,与多张表进行关联。

二、外键的作用

外键的主要作用是保证数据的一致性和完整性,并且减少数据冗余。主要体现在以下两个方面:

阻止执行

  • 从表插入新行,其外键值不是主表的主键值便阻止插入;

  • 从表修改外键值,新值不是主表的主键值便阻止修改;

  • 主表删除行,其主键值在从表里存在便阻止删除(要想删除,必须先删除从表的相关行);

  • 主表修改主键值,旧值在从表存在便阻止修改(要想修改,必须先删除从表相关行)。

级联执行

  • 主表删除行,连带从表的相关行一起删除;

  • 主表修改主键值,连带从表相关行的外键值一起修改;

三、外键创建限制

父表必须已经存在于数据库中,或者是当前正在创建的表;

如果是后一种情况,则父表和子表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性;

必须为父表定义主键;

外键中列的行数数目必须和父表的主键中列的行数数目相同;

两个表必须是 InnoDB 表,MyISAM 表暂时不支持外键;

外键列必须建立索引,MySQL 4.1.2 以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立;

外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如 inttinyint 可以,而 intchar 则不可以。

四、外键创建方法

可以在创建表时创建外键,也可以在已有的表中增加外键。

以下主要讲如何给已有的表添加外键。

1、创建外键的语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中,ON DELETEON UPDATE 表示事件触发限制,各参数意义如下:

参数

意义

RESTRICT

限制外表中的外键改动(默认值,也是最安全的设置)

CASCADE

跟随外键改动

SET NULL

设为null值

NO ACTION

无动作

SET DEFAULT

设为默认值

五、案例

  1. 创建两张表

CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `avatar` varchar(255) NULL COMMENT '头像',
  `nick_name` varchar(255) CHARACTER SET utf32 NULL COMMENT '用户昵称',
  `phone` varchar(11) CHARACTER SET utf32 NOT NULL COMMENT '用户手机号',
  `wx_id` int NULL COMMENT '微信用户表主键ID',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `wx_user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `open_id` varchar(255) NOT NULL COMMENT '小程序唯一用户标识',
  `union_id` varchar(255) NOT NULL COMMENT '开放平台唯一用户标识',
  `create_time` datetime NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  1. 建立外键

ALTER TABLE `user` ADD CONSTRAINT `fk_user_wx_user_1` FOREIGN KEY (`wx_id`) REFERENCES `wx_user` (`id`) ;

触发限制使用默认值 RESTRICT 的情况;

  1. 从表插入新行,外键值不存在于主表中,被阻止;

    INSERT INTO `user`(nick_name,phone,wx_id,create_time,update_time) VALUES('章三','1319584xxxx',1,NOW(),NOW());

    报如下错误:Cannot add or update a child row: a foreign key constraint fails (`default`.`user`, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES wx_user (`id`) ON DELETE SET NULL)

  2. 从表修改外键值,新值不在主表的主键中,阻止修改;

    INSERT INTO wx_user(open_id,union_id,create_time,update_time) VALUES ('1111111111111111','22222222222222',NOW(),NOW());
    
    INSERT INTO user(nick_name,phone,wx_id,create_time,update_time) VALUES ('张三','1312635xxxx',1,NOW(),NOW());
    
    UPDATE `default`.`user` SET `wx_id` = 2 WHERE `id` = 1;

    修改报错:Cannot add or update a child row: a foreign key constraint fails (`default`.`user`, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES wx_user (`id`) ON DELETE SET NULL)

    因为主表 wx_user 表中没有 主键Id为2 的数据。

  3. 主表删除行,其主键值在从表里存在便阻止删除(要想删除,必须先删除从表的相关行)

    DELETE from wx_user WHERE id =1;

    报如下错误:Cannot delete or update a parent row: a foreign key constraint fails (`default`.`user`, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES wx_user (`id`));

  4. 主表修改主键值,旧值在从表里存在便阻止修改(要想修改,必须先删除从表的相关行)

    UPDATE `wx_user` SET id = 3 WHERE id =1;

    报如下错误:Cannot delete or update a parent row: a foreign key constraint fails (`default`.`user`, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES wx_user (`id`))

六、更改外键事件触发限制为 CASCADE

# 删除旧外键
ALTER TABLE `user` DROP FOREIGN KEY fk_user_wx_user_1

添加新的外键,修改事件触发限制为CASCADE
ALTER TABLE `user` ADD CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES `wx_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

此时两张表中的数据:

当主表修改主键值,从表中相关行的外键值将一起修改:

UPDATE `wx_user` SET id = 3 WHERE id =1;

如果主表删除行,从表中的相关行将一起被删除:

DELETE from wx_user WHERE id = 3;

七、结论

事件触发限制条件的不同,会造成两张表中的操作限制不同,其他几个限制条件相对好理解,大家可以自己进行尝试,体会其中的区别。

参考文章:https://blog.csdn.net/weiguang102/article/details/126409406


评论