关于外键的使用

1、被引用的外键必须是唯一的

可以设置成primary key或者设置成unique

比如：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

create table customerss(cust_id int primary key, order_item int unique);

则可以执行

alter table orderitems add constraint fk_orderitems_orders1 foreign key (order_num) references customerss (order_item);

则为orderitems设置了外键

 

2、外键是为了保证数据的完整性

如果咱们现在orderitems已经有数据比如：

INSERT INTO orderitems(order_num, order_item)
VALUES(20005, 1);

而customerss依旧是空的，执行

alter table orderitems add constraint fk_orderitems_orders1 foreign key (order_num) references customerss (order_item);

则会出现报错：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`study_mysql_db`.`#sql-2ef0_11`, CONSTRAINT `fk_orderitems_order_item` FOREIGN KEY (`order_item`) REFERENCES `customerss` (`order_item`))

因为增加orderitems.order_num外键，是为了保证该列的数据完整性。但是被引用的外键，却没有该值。与完整性互斥。

 

3、外键的删除命令

当不需要外键的时候，可以执行外键的删除命令

alter table customerss drop foreign key fk_orderitems_orders1;

4、慎用set foreign_key_checks = 0;

当我们要删除一个表的时候执行：drop table customerss;

偶尔会出现错误：本表有被外键引用。

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

网上说的是

set foreign_key_checks = 0;
drop table customerss;
set foreign_key_checks = 1;

这样子确实能正常操作，并且orderitems.order_num的外键也自动删除了。

但是这样子的操作太粗暴了，会引发一些不必要的问题。

比如：

我重新再建表

create table customerss(cust_id int primary key, order_item int);

注意，order_item没有设置唯一，则会出错：ERROR 1215 (HY000): Cannot add foreign key constraint

需用使用

create table customerss(cust_id int primary key, order_item int unique);

是不是因为orderitems引用的外键还在呢？

我们来查看一下orderitems的外键

select * from information_schema.key_column_usage where referenced_table_name = 'orderitems';

返回的是空的，说明刚才咱们在删除表customerss的时候确实已经把该表对应的外键引用给删了

但是我们差customerss的外键引用情况的时候，发现不一样的地方

select constraint_name, column_name from information_schema.key_column_usage where referenced_table_name = 'customerss';

之前orderitems引用的外键还在

+-----------------------+-------------+
| constraint_name       | column_name |
+-----------------------+-------------+
| fk_orderitems_orders1 | order_num   |
+-----------------------+-------------+
1 row in set (0.03 sec)

所以当遇到因为有被引用外键时，不要直接用set foreign_key_checks = 0;的方式，而是要把所有引用该表的外键全部删除，再执行drop table customerss;

alter table orderitems drop foreign key fk_orderitems_orders1;