有些时候，为了保证数据的完整性，我们会选择的使用外键约束，例如教师对应的表和课程表中老师的id，这种时候就要使用外键约束了。

PS：这里不考虑表结构设计，三范式与反范式等设计问题，基于MySQL8.0

语法：

constraint 外键名 foreign key 外键字段 references 主表名(关联字段) [主表记录删除时的动作] [主表记录更新时的动作]

constraint可以省略，只是用来指定外键名

例如：

CREATE TABLE test (    course_id INT (11) NOT NULL AUTO_INCREMENT,    NAME VARCHAR (30) DEFAULT NULL,    PRIMARY KEY (course_id),    CONSTRAINT cour_id_fk FOREIGN KEY (course_id) REFERENCES teacher (teacher_id));

或者通过alter添加：

alter table course add constraint course_id_fk foreign key (course_id) references teacher(teacher_id) on delete cascade on update cascade;

PS：关联主表的column必须是索引，如果不是索引无法添加外键约束

做个测试：

mysql> CREATE TABLE test2 (    -> course_id INT (11) NOT NULL AUTO_INCREMENT,    -> identified_no INT(18) UNIQUE,    -> NAME VARCHAR (30) DEFAULT NULL,    -> PRIMARY KEY (course_id)    -> );Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE test1 (    -> course_id INT (11) NOT NULL AUTO_INCREMENT,    -> identified_no INT(18) UNIQUE,    -> NAME VARCHAR (30) DEFAULT NULL,    -> PRIMARY KEY (course_id),    -> CONSTRAINT cour_id1_fk FOREIGN KEY (NAME) REFERENCES test2 (NAME)    -> );ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'cour_id1_fk' in the referenced table 'test2'mysql> drop table if exists test2;Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE test2 (    -> course_id INT (11) NOT NULL AUTO_INCREMENT,    -> identified_no INT(18) UNIQUE,    -> NAME VARCHAR (30) DEFAULT NULL,    -> PRIMARY KEY (course_id),    -> INDEX(NAME)    -> );Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE test1 (    -> course_id INT (11) NOT NULL AUTO_INCREMENT,    -> identified_no INT(18) UNIQUE,    -> NAME VARCHAR (30) DEFAULT NULL,    -> PRIMARY KEY (course_id),    -> CONSTRAINT cour_id1_fk FOREIGN KEY (NAME) REFERENCES test2 (NAME)    -> );Query OK, 0 rows affected (0.04 sec)

结果：name列如果不是索引，无法作为外键的引用列，当我们添加name为索引，发现添加外键约束成功

外键约束的方式有四种：

1、cascade：

　　级联方式，删除/更新父表的某条记录，子表中引用该值的记录会自动被删除/更新

CREATE TABLE `teacher` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `course` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) DEFAULT NULL,  `teacher_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `course_id_fk` (`teacher_id`),  CONSTRAINT `course_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> select * from teacher;+------+-------+| id   | name  |+------+-------+| 1001 | sam   || 1002 | jesen |+------+-------+2 rows in set (0.00 sec)mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  1 | 语文   |       1001 ||  2 | 数学   |       1002 |+----+--------+------------+2 rows in set (0.00 sec)mysql> update teacher set id=1 where id=1001;　　//更新主表的id，从表的外键值会更新Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  1 | 语文   |          1 ||  2 | 数学   |       1002 |+----+--------+------------+2 rows in set (0.00 sec)mysql> delete from teacher where id=1;　　//删除主表的id，从表外键值对应的那条数据也会删除Query OK, 1 row affected (0.00 sec)mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  2 | 数学   |       1002 |+----+--------+------------+1 row in set (0.00 sec)mysql> update course set teacher_id=2 where id =1;　　//不能直接更新从表的外键ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysql`.`course`, CONSTRAINT `course_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

2、set null：

　　设置为null。主表主键值被更新或删除，从表的外键被设置为null。但注意，要求该外键列，没有not null属性约束。

先删除外键，后重建：

mysql> alter table course drop foreign key course_id_fk;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table course add constraint course_id_fk foreign key (teacher_id) references teacher(id) on delete set null on update set null;Query OK, 1 row affected (0.06 sec)Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  1 | 数学   |       1002 |+----+--------+------------+1 row in set (0.00 sec)mysql> update teacher set id=1001;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  1 | 数学   |       NULL |+----+--------+------------+1 row in set (0.00 sec)mysql> delete from teacher ;Query OK, 1 row affected (0.00 sec)mysql> select * from course;+----+--------+------------+| id | name   | teacher_id |+----+--------+------------+|  1 | 数学   |       NULL |+----+--------+------------+1 row in set (0.00 sec)

no action/restrict：

　　禁止模式，拒绝父表删除和更新

mysql> alter table course drop foreign key course_id_fk;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table course add constraint course_id_fk foreign key (teacher_id) references teacher(id) on delete no action on update restrict;Query OK, 1 row affected (0.06 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> update teacher set id=10012;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql`.`course`, CONSTRAINT `course_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON UPDATE RESTRICT)mysql> delete from teacher;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql`.`course`, CONSTRAINT `course_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON UPDATE RESTRICT)

默认：也是禁止模式

alter table course add constraint course_id_fk foreign key (teacher_id) references teacher(id);