MySQL外键约束(FOREIGN KEY)是什么?

外键约束

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;
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页