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