MySQL基础之 外键参照讲解
外键：
定义：如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。
作用：外键是用来实现参照完整性的，不同的外键约束方式将可以是两张表紧密的结合起来。比如修改或者删除的级联操作等，外键主要用来保证数据的完整性和一致性。
条件：
1、创建外键的两个表，父表与子表必须是InnoDB存储引擎。
2、创建外键的时候，父表要创建索引，一般为主键索引。子表在创建外键的时候也必须要创建索引。
3、子表的外键必须和父表的主键数据类型相对应（字段的类型和值必须一样或者相似，比如int也可以对应tinyint）。
语法：
下面这个是在子表中操作的：
[CONSTRAINT Symbol] FOREIGN KEY [id] (index_col_name,.....) REFERENCES tbl_name (index_col_name,...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
其中Symbol仅仅是给约束自定义一个名称，方便以后我们删除约束。
RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下父表不能进行操作。比如：DELETE RESTRICT表示主表在删除记录的时候，如果子表有对应的记录，则不允许删除。比如UPDATE CASCADE表示主表在更新记录的时候，如果子表有对应的记录，则子表也更新。（其实更新的就是主键和外键的那一列的值）。
CASCADE：表示父表在更新或者删除时，也更新或者删除子表对应的记录。
SET NULL：表示父表的更新或者删除的时候，那么子表对应的字段被会SET NULL。
注意：当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
例子：
首先是我们创建两个表，一个是父表，一个是字表。字表设置有参照约束。
mysql> create table country( -> country_id smallint unsigned not null auto_increment, -> country varchar(50) not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, -> primary key(country_id) -> )engine=InnoDB default charset=utf8; Query OK, 0 rows affected (0.10 sec) mysql> desc country; +-------------+----------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+-----------------------------+ | country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | country | varchar(50) | NO | | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+----------------------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.00 sec) mysql> create table city( -> city_id smallint unsigned not null auto_increment, -> city varchar(50) not null, -> country_id smallint unsigned not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, -> primary key(city_id), -> key idx_fk_country_id(country_id), -> constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade -> )engine=InnoDB default charset=utf8; Query OK, 0 rows affected (0.04 sec) mysql> desc country; +-------------+----------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+-----------------------------+ | country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | country | varchar(50) | NO | | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+----------------------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.00 sec) mysql> insert into country values(1,'English',now()); Query OK, 1 row affected (0.00 sec) mysql> select * from country; +------------+---------+---------------------+ | country_id | country | last_update | +------------+---------+---------------------+ | 1 | English | 2018-10-25 10:34:49 | +------------+---------+---------------------+ 1 row in set (0.00 sec) mysql> desc city; +-------------+----------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+-----------------------------+ | city_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | city | varchar(50) | NO | | NULL | | | country_id | smallint(5) unsigned | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+----------------------+------+-----+-------------------+-----------------------------+ 4 rows in set (0.00 sec) mysql> insert into city values(251,'Kabul',1,now()); Query OK, 1 row affected (0.00 sec) mysql> select * from city; +---------+-------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+-------+------------+---------------------+ | 251 | Kabul | 1 | 2018-10-25 10:36:51 | +---------+-------+------------+---------------------+ 1 row in set (0.00 sec)
上面的“on update current_timestamp”表示更新表的记录的时候 ，也自动更新修改的时间。一句话“在创建新记录和修改现有记录的时候都对这个数据列刷新”。
1、现在我们删除父表中的一个数据记录。
mysql> delete from country where country_id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`exercise`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
我们发现是删除不了的，因为字表参照了父表的一个记录（我们在字表参照里面设置了“限制在字表有关联的情况下父表不能删除”---delete restrict）
2、然后我们更新一个记录看看。
mysql> update country set country_id=251 where country='English'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql> SET SQL_SAFE_UPDATES=0; #我是以安全模式启动的mysql，设置一下就可以了 Query OK, 0 rows affected (0.00 sec) mysql> update country set country_id=251 where country='English'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from country; +------------+---------+---------------------+ | country_id | country | last_update | +------------+---------+---------------------+ | 251 | English | 2018-10-25 10:47:01 | +------------+---------+---------------------+ 1 row in set (0.00 sec) mysql> select * from city; +---------+-------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+-------+------------+---------------------+ | 251 | Kabul | 251 | 2018-10-25 10:38:28 | +---------+-------+------------+---------------------+ 1 row in set (0.00 sec)
我们更新的父表的主键记录，发现子表的外键记录也自动更新了，因为子表的外键参照父表的主键。并且子表在约束条件里面写了"update cascade"。
3、现在我们删除父表的主键
mysql> alter table country drop column country_id; ERROR 1829 (HY000): Cannot drop column 'country_id': needed in a foreign key constraint 'fk_city_country' of table 'exercise.city'
当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
还有一些需要注意的：
在导入多个表的数据的时候，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查，在执行load data和alter table操作的时候，可以暂时关闭外键约束来加快处理的速度。关闭的指令是“SET FORENGN_KEY_CHECKS=0”；执行完成之后，通过执行"SET FOREIGN_KEY_CHECKS=1"语句改回原始状态。
