mysql基础(34)_外键

最新推荐文章于 2021-05-23 02:07:51 发布

风之舞_yjf

最新推荐文章于 2021-05-23 02:07:51 发布

阅读量129

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/no15870678621/article/details/116175123

版权

MySQL 专栏收录该内容

55 篇文章 3 订阅

订阅专栏

外键(foreign key)

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键，由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表，外键又称作外关键字。

比如一张表(A)中有一个字段，保存的值指向另外一张表(B)的主键，那么：
B：主表
A: 从表

外键的操作

增加外键
mysql中提供了两种方式增加外键：

1、方案1：在创建表的时候增加外键（类似主键）
基本语法：在字段之后增加一条语句：[constraint '外键名']foreign key(外键字段) references 主表(主键)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
-- 存储引擎myisam不支持外键，所以必须修改
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                         |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL COMMENT '主键，学生id',
  `name` varchar(10) NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table student engine=innodb;
Query OK, 4 rows affected (2.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
-- 设置外键记住字段类型必须一致！！！就算是int和int(11)也不行
mysql> create table foreign_key(
    -> id int primary key auto_increment,
    -> name varchar(10) not null,
    -> class_id int(11),
    -> foreign key(class_id) references student(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.48 sec)

mysql> desc foreign_key;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| class_id | int(11)     | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
--  创建外键自动增加的普通索引：KEY `class_id` (`class_id`),外键索引CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`)
mysql> show create table foreign_key;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                              |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foreign_key | CREATE TABLE `foreign_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MUL：多索引，外键本身是一个索引，外键要求外键字段本身也是一种普通索引（在含有外键表详细结构，如上所示中：KEY `class_id` (`class_id`),为外键创建时自动增加的普通索引 CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`)为外键索引）

2、方案2：在创建表后增加外键
alter table 从表 add [constraint '外键名'] foreign key(外键字段) references 主表(主键);

-- 错误是因为：删除外键名而不是外键字段
mysql> alter table foreign_key drop foreign key class_id;
ERROR 1025 (HY000): Error on rename of '.\test\foreign_key' to '.\test\#sql2-88c-1' (errno: 152)
-- 删除外键名成功！
mysql> alter table foreign_key drop foreign key foreign_key_ibfk_1;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 删除普通索引，之前的普通索引是：KEY `class_id` (`class_id`)，所以要删除。
mysql> alter table foreign_key drop index class_id;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table foreign_key;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                       |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foreign_key | CREATE TABLE `foreign_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 增加外键名为foreign_key_ibfk_1的外键字段class_id
mysql> alter table foreign_key add CONSTRAINT `foreign_key_ibfk_1` foreign key(class_id) references student(id);
Query OK, 0 rows affected (2.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 可以看到添加成功了
mysql> show create table foreign_key;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                        |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foreign_key | CREATE TABLE `foreign_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreign_key_ibfk_1` (`class_id`),
  CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

外键名可以指定。引入外键之后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的参照完整性。

mysql> insert into foreign_key values(null,'张三','3');
Query OK, 1 row affected, 1 warning (1.81 sec)

mysql> insert into foreign_key values(null,'张三','6');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`foreign_key`, CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`))
-- 乱码问题是没设置utf8，插入成功的前提是，只包含student表中的id值
mysql> select * from foreign_key;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | ??   |        3 |
+----+------+----------+
1 row in set (0.00 sec)

备注：在mysql 中建立引用约束的时候会出现MySQL ERROR 1005: Can't create table (errno: 150)的错误信息结果是不能建立引用约束。出现问题的大致情况：
1、外键的引用类型不一样，如主键是int外键是char
2、找不到主表中引用的列
3、主键和外键的字符编码不一致，也可能存储引擎不一样

修改&删除外键（如上图所示）

外键不允许修改，只能先删除后增加
基本语法：alter table 从表 drop foreign key 外键名字;

外键不能删除产生的普通索引，只会删除外键，如果想删除对应的索引：
alter table 表名 drop index 索引名字;

外键基本要求
1、外键字段需要保证与关联的主表的主键字段类型完全一致
2、基本属性也要相同
3、如果是在表后增加外键，对数据还有一定的要求(从表数据与主表的关联关系)
4、外键只能使用innodb存储引擎：myisam不支持

风之舞_yjf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql基础(34)_外键

外键(foreign key)外键概念如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键，由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表，外键又称作外关键字。比如一张表(A)中有一个字段，保存的值指向另外一张表(B)的主键，那么：B：主表A: 从表外键的操作增加外键mysql中提供了两种方式增加外键：1、方案1：在创建表的时候增加外键（类似主键）基本语法：在字段之后增加一条语句
复制链接

扫一扫