一、定义

• 引用完整性是对实体之间关系的描述，是定义外关键字与主关键字之间的引用规则，也就是外键约束。如果要删除被引用的对象，也要删除引用它的所有对象，或把引用值设置为空。外键指引用另一个表中的一列或多列，被引用的列应该具有主键约束或者唯一约束。外键用于加强两个表，数据之间的连接。

1.添加外键约束

语法格式

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

示例

将具有唯一性约束字段age的stu表作为主表，将student表作为从表，为student表age字段添加外键约束

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加外键约束

mysql> alter table student add foreign key(age) references stu(age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看student表具体信息，如下，外键约束添加成功，‘student_ibfk_1’为外键约束名

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`age`) REFERENCES `stu` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

创建表时添加外键约束

首先创建一个含有主键的表student2

mysql>  create table student2(
    -> stu_id int primary key,
    -> stu_name varchar(50)
    -> );

创建表student3时添加外键约束

mysql>  create table student3(
    -> id int,
    -> name varchar(50),
    -> stu_id int,
    -> foreign key(stu_id) references student2(stu_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

查看表的具体信息，如下，外键约束创建成功

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`age`) REFERENCES `stu` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

2.删除外键约束

即解除两个表之间的关联关系。

alter table 表名 drop foreign key 外键名;

示例，删除student表外键约束

mysql> alter table student drop foreign key student_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如上，外键约束删除成功

mysql>  show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

但是主键还没有彻底删除！

采用desc语句看一下表结构，可以看到age字段的key值仍然有值

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这是因为MySQL在创建外键后会自动建一个同名的索引。
因此，采用上面语句只能删除外键约束，无法彻底删除外键。
所以，我们需要将同名索引也删除，才可以将外键彻底删除

首先，我们需要使用show index from语句查看索引，如下。

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          1 | age      |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

我们再使用删除索引的语句将索引删除

mysql> alter table student drop index age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次使用desc语句查看表结构，key列里的MUl消失了

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这样主键就彻底删除了！