signed

QiShunwang

“诚信为本、客户至上”

mysql基础(34)_外键

2021/4/26 22:37:32   来源:

外键(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不支持