MYSQL外键

最新推荐文章于 2025-08-05 17:47:02 发布

li_jiaxiang

最新推荐文章于 2025-08-05 17:47:02 发布

阅读量344

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/li_jiaxiang/article/details/78193760

mysql 专栏收录该内容

4 篇文章

订阅专栏

请注意 :
1、外键约束只能在innodb引擎下使用。
2、外键应该在建立子表的时候建立。
3、外键所建立的字段，其字段类型应该与父表中对应的字段类型相同。例如同为INT。若不同在建立外键时会报一个150的错误码。

下面我们先来创建一张主表

mysql> create table grades(
    -> id tinyint not null auto_increment primary key comment '自增主键',
    -> grade_name varchar(40) not null comment '年级名称'
    -> )engine=innodb default charset=utf8 comment '年级表';
Query OK, 0 rows affected (0.00 sec)

mysql> desc grades;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| grade_name | varchar(40) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

可以看到我们已经成功创建了一张年级表，接下来我们再创建一张学生表，并且为学生表创建外键，关联年级表的id字段。

注意关键语法：constraint 外键名 foreign key(需要设置外键的字段) references 父表(父表关联的字段)

mysql> create table students (
    -> id int not null auto_increment primary key comment '自增id',
    -> name varchar(45) not null comment '学生姓名',
    -> g_id tinyint not null comment '年级ID 后面会设置外键约束',
    -> constraint fk_grade_id foreign key(g_id) references grades(`id`)
    -> )engine=innodb default charset=utf8 comment '设置了外键约束的学生表';
Query OK, 0 rows affected (0.01 sec)

设置外键联动

但是我们仅仅是建立了外键，并没有指定它的联动关系，所以这个外键实际上是并没有什么用处的。
外键的联动可以通过on delete和on update来触发。

触发的效果有三种：
1、cascade关联操作，如果主表被更新或删除，从表也会执行相应的操作
2、set null,表示从表数据不指向主表任何记录即当主表数据更新后不存在时将从表的外键值设为空
3、restrict：拒绝主表的相关操作即主表没有的数据都不能添加或更新

设置外键的正确姿势应该是constraint 外键名 foreign key(需要设置外键的字段) references 父表(父表关联的字段) on delete/update cascade/restrict/set null ;

删除错误的外键

我们现在来删除一开始创建的并没有什么卵用的外键

mysql> alter table students
    -> drop foreign key 
    -> fk_grade_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

在已有的表上创建一个新的外键

mysql> alter table students  add constraint fk_id foreign key(g_id) references grades(`id`) on update restrict on delete cascade;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：on delete 时不能设置动作为 set null 否则会报一个ERROR 1005 (HY000): Can’t create table ‘demo.#sql-1fcc_3’ (errno: 150)的错误

解释一下上面的sql：修改表students ，新增一个外键约束，外键约束的名称是
fk_id,关联的父表是grades关联键名是父表下的id设置约束为，更新操作如果父表没有值，则子表拒绝操作。删除动作，子表随父表而执行。

新的外键约束已经创建完成，我们来测试一下。
首先在父表没有数据的情况下向子表内插入一条记录：

mysql> insert into students(name,g_id) values('lisi',2);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo`.`students`, CONSTRAINT `fk_id` FOREIGN KEY (`g_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE)

可以看到报了一个错误，无法插入。接下来我们先在父表写入一条测试数据，然后再在子表插入。

mysql> insert into grades(grade_name) values('一年级');
Query OK, 1 row affected (0.00 sec)

mysql> select * from grades;
+----+------------+
| id | grade_name |
+----+------------+
|  1 | 一年级     |
+----+------------+
1 row in set (0.00 sec)

mysql> insert into students(name,g_id) values('lisi',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+------+------+
| id | name | g_id |
+----+------+------+
|  2 | lisi |    1 |
+----+------+------+
1 row in set (0.00 sec)

可以看到插入成功了。下面我们删除父表中的数据，然后看子表

mysql> delete from grades where id =1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from grades;
Empty set (0.00 sec)

mysql> select * from students;
Empty set (0.00 sec)

可见，删除联动也没有问题。