MySQL外键约束（FOREIGN KEY）是什么？

您所在的位置：网站首页 › maya4键是什么 › MySQL外键约束（FOREIGN KEY）是什么？

MySQL外键约束（FOREIGN KEY）是什么？

2024-02-24 08:39| 来源: 网络整理| 查看: 265

向导外键约束外键创建规则创建外键约束语法外键约束创建修改创建外键约束删除外键完整格式创建

外键约束

MySQL的外键约束用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。

其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。

外键约束主要作用就是能够让表与表之间的数据建立关联，使数据更加完整，关联性更强。

关于完整性，可以通过一个例子来说明

假如有两种表，一张用户账户表（用于存储用户账户），一张是账户信息表（用于存储账户中的信息）。

1）我不小心将用户账户表中的某个用户删除了，那么账户信息表中与这个用户有关的数据就变成无源数据了，找不到其属于哪个用户账户，导致用户信息不完整。

2）我在账户信息表中随便添加了一条数据，而其在用户账户表中没有对应的用户，这样用户信息也是不完整的。

如果有了外键约束，将用户账户表设为主表，账户信息表设为从表，那么就无法直接往账户信息表中插入数据，在用户账户表中删除用户，账户信息表中的用户信息也会被删除。外键约束的方式可以使两张紧密的联系在一起，保障数据完整性和一致性的同时，日常维护也更加方便。

外键创建规则

1）必须有主表才可以设置从表。

2）主表必须实际存在。

3）必须为主表定义主键。

4）外键列的数据类型必须和主键列的数据类型相同。

5）外键列的数量必须和主键列的数量相同。

6）外键可以不是外表中的主键，但必须和主表关联字段相对应。

7）主从表创建时，存储引擎必须是InnoDB。

创建外键约束语法

create table 表名（字段定义… CONSTRAINT `外键名称` FOREIGN KEY （字段）REFERENCES 主表名（主键字段）属性）；

参数说明： CONSTRAINT ：用于设置外键约束名称，可以省略

FOREIGN KEY：外键设置，用于指定外键字段

REFERENCES：主表及主键设置，用于指定主表和主键

属性说明：

CASCADE：主表删除或修改记录时，从表也会对关联记录的外键字段进行修改。

RESTRICT：删除或修改主表记录，子表中若有关联记录，则不允许主表删除或修改。

SET NULL：主表删除或修改主表记录时，从表会将关联记录的外键字段设为null。

ON UPDATE CASCADE：主表修改记录时，从表关联记录的外键字段也会修改。（将CASCADE改为RESTRICT，意思相反）

ON DELETE CASCADE：主表删除记录时，从表关联记录的外键字段也会删除。（将CASCADE改为RESTRICT，意思相反）

外键约束创建

创建两个表，主表为class，从表为students

主表：

mysql> create table class( xuehao int primary key, name varchar(6))engine=innodb;

从表：

mysql> create table students( id int auto_increment primary key, uid int not null, name varchar(6) not null, foreign key(uid) references class(xuehao) on update cascade on delete cascade)engine=innodb;

插入数据，进行测试

mysql> insert into class values(111,'张三'),(222,'李四'),(333,'王五'); mysql> insert into students values(1,111,'张三'),(2,222,'李四'),(3,333,'王五');

查看创建的记录

mysql> select * from class; +--------+--------+ | xuehao | name | +--------+--------+ | 111 | 张三 | | 222 | 李四 | | 333 | 王五 | +--------+--------+ mysql> select * from students; +----+-----+--------+ | id | uid | name | +----+-----+--------+ | 1 | 111 | 张三 | | 2 | 222 | 李四 | | 3 | 333 | 王五 | +----+-----+--------+

验证外键约束特性，删除从表中id为1的记录

mysql> delete from students where id=1; mysql> select * from students; +----+-----+--------+ | id | uid | name | +----+-----+--------+ | 2 | 222 | 李四 | | 3 | 333 | 王五 | +----+-----+--------+ mysql> select * from class; +--------+--------+ | xuehao | name | +--------+--------+ | 111 | 张三 | | 222 | 李四 | | 333 | 王五 | +--------+--------+

从表中删除记录不会对主表造成影响。（从删主不删）

在主表中删除学号为111的记录

mysql> insert into students values(1,111,'张三'); 将删除的记录从新插入 mysql> select * from students; +----+-----+--------+ | id | uid | name | +----+-----+--------+ | 1 | 111 | 张三 | | 2 | 222 | 李四 | | 3 | 333 | 王五 | +----+-----+--------+ mysql> delete from class where xuehao=111; mysql> select * from students; +----+-----+--------+ | id | uid | name | +----+-----+--------+ | 2 | 222 | 李四 | | 3 | 333 | 王五 | +----+-----+--------+ 2 rows in set (0.00 sec)

主表的修改根据属性会对从表造成影响。（主删从不见）

mysql> insert into students values(4,444,'赵六'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `class` (`xuehao`) ON DELETE CASCADE ON UPDATE CASCADE)

无法插入主键关联中不存在的记录。（class表的xuehao字段中没有444，所以与其关联的students表中uid为444的记录也无法插入）

只有主表存在，从表才可插入，解决方案就是给主表插入xuehao为444的字段。

mysql> insert into class values(444,'赵六'); mysql> insert into students values(4,444,'赵六'); mysql> select * from students; +----+-----+--------+ | id | uid | name | +----+-----+--------+ | 2 | 222 | 李四 | | 3 | 333 | 王五 | | 4 | 444 | 赵六 | +----+-----+--------+ 修改创建外键约束

语法：

alter table 表名 add constraint `外键名称` foreign key （外键字段）references 主表（主键）属性）；

将之前的students表删除，从新创建。

mysql> drop table students; mysql> create table students(id int primary key auto_increment,uid int not null,name varchar(6) not null);

将其修改为class表的外键约束

mysql> alter table students add foreign key(uid) references class(xuehao) on update cascade on delete cascade;

通过查看表执行命令，即可查询外键约束是否创建完成

mysql> show create table students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `name` varchar(6) NOT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`), CONSTRAINT `students_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `class` (`xuehao`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中CONSTRAINT `students_ibfk_1`字样就是外键的名称，没有手动指定，外键名称是数据库设置的，在删除外键时会用到外键名称。

删除外键

语法：

alter table 表名 drop foreign key 外键名称；

将创建的外键删除

mysql> alter table students drop foreign key students_ibfk_1;

再次查看创建表的命令，发现与外键相关的设置消失了

mysql> show create table students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `name` varchar(6) NOT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 完整格式创建

在之前的举例创建中，偷懒将可以省略的外键名称设置给省略了，为外键设置一个名称，在删除的时候还是比较方便的，系统自动创建的有点长。

删除原students表

mysql> drop table students;

从新创建，并且建立外键

mysql> create table students( -> id int primary key auto_increment, -> uid int not null, -> name varchar(6) not null, -> constraint `qwq` foreign key (uid) references class(xuehao) -> on update cascade on delete cascade -> )engine=innodb;

【本文地址】

公司简介

联系我们