MySQL系列(十一)--外键约束foreign key的基本使用

2019/07/10 18:25
阅读数 202

有些时候,为了保证数据的完整性,我们会选择的使用外键约束,例如教师对应的表和课程表中老师的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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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);

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部