外键

外键

什么是外键？
让当前表字段的值在另一个表中字段值的范围内选择。

作用：限制如何给字段赋值(给当前表中字段赋值时,值只能在与其相关联的表中的指定字段值的范围里选择)
条件：表的存储引擎必须是innodb,字段类型必须要一致，被参照字段必须要是索引字段的一种，如：主键primary key
#############################################################################
创建外键

用法：
foreign key 表A(字段名)
references 表B(字段名)
on update cascade
on delete cascade

mysql> create table yg(
-> yg_id int primary key auto_increment,
-> name char(10)
-> )engine=innodb;

mysql> insert into yg(name) values("bob");
mysql> insert into yg(name) values("tom");

mysql> create table gz(
-> gz_id int,
-> pay float(7,2),
-> foreign key(gz_id) references yg(yg_id)
-> on update cascade on delete cascade
-> )engine=innodb; 如果当前默认的存储引擎是别的话,要指定表的存储引擎是innodb

MariaDB [db1]> show create table gz\G; 查看表的创造过程
页面显示如下：
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float(7,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

##########################################################
测试外键

mysql> select * from yg;
+-------+------+
| yg_id | name |
+-------+------+
| 1 | bob |
| 2 | tom |
+-------+------+

mysql> desc gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | YES | MUL | NULL | |
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+

mysql> insert into gz values(1,45000);
mysql> insert into gz values(2,25000);

mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 2 | 25000.00 |
+-------+----------+
##########################################################################
常见错误：因为关联的外键的原表中还没有对应信息，所以插入数据时报错
如：员工id3还不存在，所有无法发工资

mysql> insert into gz values(3,98000);
错误信息提示：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> insert into yg(name) values("harry"); #先创建员工id3

mysql> select * from yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | bob |
| 2 | tom |
| 3 | harry |
+-------+-------+

mysql> insert into gz values(3,98000); 插入数据成功

mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 2 | 25000.00 |
| 3 | 98000.00 |
+-------+----------+
############################################################################
mysql> delete from yg where yg_id=3; #删除某个员工编号

mysql> update yg set yg_id=8 where yg_id=2; #修改某个员工编号，并且同步更新关联的外键，从2变成8

mysql> select * from yg;
+-------+------+
| yg_id | name |
+-------+------+
| 1 | bob |
| 8 | tom |
+-------+------+

mysql> select * from gz; #它会自动同步更新员工编号，从2变成8
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 8 | 25000.00 |
+-------+----------+
##############################################################
mysql> insert into gz values(8,90000); #同一个员工还可以重复发工资

mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 8 | 25000.00 |
| 8 | 90000.00 |
+-------+----------+

mysql> insert into gz values(8,5000); #同一个员工还可以重复发工资
mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 8 | 25000.00 |
| 8 | 90000.00 |
| 8 | 5000.00 |
+-------+----------+

mysql> insert into gz values(null,6000); #给空发工资，居然也可以发
mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 45000.00 |
| 8 | 25000.00 |
| 8 | 90000.00 |
| 8 | 5000.00 |
| NULL | 6000.00 |
+-------+----------+

mysql> delete from gz;
mysql> select * from gz;
Empty set (0.00 sec)

mysql> desc gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | YES | MUL | NULL | |
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+

mysql> alter table gz add primary key(gz_id); #设置为主键，就可以避免重复发工资了。

mysql> desc gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | NO | PRI | NULL | |
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
#####################################################################
mysql> insert into gz values(1,25000);
mysql> insert into gz values(8,30000);

mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 25000.00 |
| 8 | 30000.00 |
+-------+----------+

mysql> insert into gz values(1,9200); #不能给同一个员工重复发工资了
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
########################################################################################
删除外键

用法：
alter table 表名 drop foreign key 字段名;
如：alter table gz drop foreign key gz_ibfk_1;
#########################################################################################
常见错误：删除表、表字段失败。

常见错误：删除表失败。
原因：被参考的表、表中的字段不允许被删除
mysql> drop table yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

常见错误：删除表中字段失败。
原因：被参考的表、表中的字段不允许被删除
mysql> alter table yg drop yg_id; 无法删除和外键有关联的员工编号yg_id这个字段
ERROR 1829 (HY000): Cannot drop column 'yg_id': needed in a foreign key constraint 'gz_ibfk_1' of table 'db2.gz'

解决办法：删除其他表对删除表或字段的参考
mysql> alter table gz drop foreign key gz_ibfk_1; #先取消外键

然后就能成功删除表、表字段
mysql> alter table yg drop yg_id; #成功删除员工编号yg_id这个字段
mysql> drop table yg; #成功删除表
######################################################################
mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 25000.00 |
| 8 | 30000.00 |
+-------+----------+

mysql> insert into gz values(3,5000); #取消外键后，可以随意给不存在的员工发工资
mysql> insert into gz values(20,10000);

mysql> select * from gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 25000.00 |
| 3 | 5000.00 |
| 8 | 30000.00 |
| 20 | 10000.00 |
+-------+----------+
MariaDB [db1]> drop table c,d,t4; 可删除多个表