一、什么是外键

外键也称之为外键约束 : 关键字foreign key

外键：外面的键，一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键。

外键所在的表称之为子表(附表)；外键所指向的主键所在的表称之为父表(主表)

二、如何加外键

将一个表的字段与另外一张表的主键进行关联(实体与实体之间的联系)

方案1: 在创建表的时候就增加外键:在表字段之后使用 foreign key

mysql> -- 创建主表mysql> CREATE TABLE person (-> id int NOT NULL AUTO_INCREMENT,-> name varchar(20) DEFAULT NULL,-> age int DEFAULT NULL,-> PRIMARY KEY (id)-> ) ;Query OK, 0 rows affected (0.04 sec)mysql> -- 创建子表mysql> CREATE TABLE info (-> id int NOT NULL AUTO_INCREMENT,-> person_info varchar(100) DEFAULT NULL,-> p_id int NOT NULL,-> PRIMARY KEY(id),-> -- 建立外键约束-> constraint fk_p_id foreign key (p_id) references person(id)-> );Query OK, 0 rows affected (0.04 sec)

方案2: 在创建表之后增加外键: 指定外键名字

alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段)

查看指定的外键名

mysql> desc info;+-------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra|+-------------+--------------+------+-----+---------+----------------+| id| int| NO | PRI | NULL | auto_increment || person_info | varchar(100) | YES || NULL ||| p_id | int| NO | MUL | NULL ||+-------------+--------------+------+-----+---------+----------------+3 rows in set (0.01 sec)

或

mysql> show create info;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'info' at line 1mysql> show create table info;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| info | CREATE TABLE `info` (`id` int NOT NULL AUTO_INCREMENT,`person_info` varchar(100) DEFAULT NULL,`p_id` int NOT NULL,PRIMARY KEY (`id`),KEY `fk_p_id` (`p_id`),CONSTRAINT `fk_p_id` FOREIGN KEY (`p_id`) REFERENCES `person` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

外键增加的基础条件: 外键字段必须与引用表(父表主键)的数据类型严格保持一致

三、删除外键

外键不能被修改，只能先删除后新增。

alter table 表名 drop foreign key 外键名

mysql> alter table info drop foreign key fk_p_id;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0

四、外键的作用

1、外键对子表的数据写操作约束：

(增加和更新): 如果子表中插入的数据所对应的外键在父表不存在:则不能成功.

现在两张表都是空的，我们先尝试往info表中插入一条数据：

mysql> insert into info(person_info,p_id) values('info',1);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`info`, CONSTRAINT `fk_p_id` FOREIGN KEY (`p_id`) REFERENCES `person` (`id`))

发现是不允许的！

我们往person中插入一条数据：

mysql> insert into person(name,age) values('cjian',18);Query OK, 1 row affected (0.01 sec)mysql> select * from person;+----+-------+------+| id | name | age |+----+-------+------+| 1 | cjian | 18 |+----+-------+------+1 row in set (0.01 sec)

再次执行往info插入数据的sql：

mysql> insert into info(person_info,p_id) values('info',1);Query OK, 1 row affected (0.00 sec)mysql> select * from info;+----+-------------+------+| id | person_info | p_id |+----+-------------+------+| 1 | info | 1 |+----+-------------+------+1 row in set (0.00 sec)

发现插入成功~

2、外键对父表的数据约束

当父表操作一个记录,但是该记录被子表所引用的时候,那么父表的操作将会被限制(更新: 主键和删除)

接着尝试删除person表的id=1的记录：

mysql> delete from person where id=1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`info`, CONSTRAINT `fk_p_id` FOREIGN KEY (`p_id`) REFERENCES `person` (`id`))

发现无法删除！

五、控制约束

外键约束：可以通过在建立外键的时候，对外键进行约束控制。

约束控制有三种模式

严格模式: district(默认的)

置空模式: set null,对子表的限制: 当父表删除一个被子表引用的记录的时候,会自动的将子表中对应的父表引用(外键)设置成NULL

级联模式: cascade, 级联操作: 当父表对一个被子表引用的数据进行操作的时候,会自动的连带更新子表对应的数据.(更新操作)

模式设定语法: 在外键增加之后(foreign key(外键字段) references 父表(主键)),增加on关键字, 指定操作方式和约束模式. 一个常用的约束模式如下

on update cascade – 级联操作: 父表更新,子表跟着变

on delete set null; – 父表删除, 子表置空

mysql> alter table info drop foreign key fk_p_id;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table info add constraint fk_p_id foreign key(p_id) references person(id) on update cascade on delete set null;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0

测试更新：

mysql> update person set id =2 where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from info;+----+-------------+------+| id | person_info | p_id |+----+-------------+------+| 1 | info | 2 |+----+-------------+------+1 row in set (0.00 sec)

测试删除：

mysql> delete from person where id =2;Query OK, 1 row affected (0.00 sec)mysql> select * from info;+----+-------------+------+| id | person_info | p_id |+----+-------------+------+| 1 | info | NULL |+----+-------------+------+1 row in set (0.00 sec)