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

时间:2021-10-06 13:20:43

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

MySQL系列(十一)--外键约束foreign key的基本使用的更多相关文章

  1. 外键约束 foreign key

    外键约束 :保持数据一致性,完整性实现一对多关系.外键必须关联到键上面去,一般情况是,关联到另一张表的主键 (因为一个表只存一类信息.用外键来做参照,保证数据的一致性,可以减少数据冗余) ##表acr ...

  2. 关于数据库主从表、主键PRIMARY KEY 外键约束 FOREIGN KEY 约束----NOT NULL,DEFAULT,CHECK

    如果由两个列共同组成主键,而且一个子表将主键作为可为空值的外键来继承,就可能得到错误的数据.可在一个外键列中插入有效的值,但在另一个外键列中插入空值.然后,可添加一个数据表检查约束,在可为空的外键中检 ...

  3. mysql 外键(FOREIGN KEY)

    最近有开始做一个实验室管理系统,因为分了几个表进行存储·所以要维护表间的关联··研究了一下MySQL的外键. (1)只有InnoDB类型的表才可以使用外键,mysql默认是MyISAM,这种类型不支持 ...

  4. MySQL(10):实体、实体表和外键(foreign key)

    1.实体        数据库管理系统中的各种用于数据管理方便而设定的各种数据管理对象,如:数据库表.视图.存储过程等都是数据库实体.广义上讲,这些对象中所存储的数据也是数据库实体.因为它们也是确切存 ...

  5. 如何在MySQL中设置外键约束

    引用:http://blog.sina.com.cn/s/blog_53729e4601011wja.html MySql外键设置详解   (1) 外键的使用: 外键的作用,主要有两个:    一个是 ...

  6. MySQL truncate含有外键约束的条目报错

    1.报错信息: Cannot truncate a table referenced in a foreign key constraint 2.出现错误操作: truncate table a1; ...

  7. 【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复)

    约束 外键约束 外键约束概念 让表和表之间产生关系,从而保证数据的准确性! 建表时添加外键约束 为什么要有外键约束 -- 创建db2数据库 CREATE DATABASE db2; -- 使用db2数 ...

  8. 如何在MySQL中设置外键约束以及外键的作用

    1.外键的作用,主要有两个:     一个是让数据库自己通过外键来保证数据的完整性和一致性     一个就是能够增加ER图的可读性 2.外键的配置 1)先创建一个主表,代码如下: #创建表studen ...

  9. SET FOREIGN_KEY_CHECKS=0;在Mysql中取消外键约束。

    SET FOREIGN_KEY_CHECKS=0;在Mysql中取消外键约束.

随机推荐

  1. 读取xml文件报错:Invalid byte 2 of 2-byte UTF-8 sequence。

    程序读取xml文件后,系统报“Invalid byte 2 of 2-byte UTF-8 sequence”错误,如何解决呢? 1.程序解析xml的时候,出现Invalid byte 2 of 2- ...

  2. [BZOJ 2241][SDOI2011]打地鼠(枚举+预处理)

    题目:http://www.lydsy.com:808/JudgeOnline/problem.php?id=2241 分析: 鉴于R,C的取值很小,于是可以人为枚举R和C的大小,然后判定这个规格的锤 ...

  3. oracle在linux配置信息

    这两天在linux中给已有的oracle添加新实例,其中涉及数据库服务.监听配置,oracle服务是否正常.监听是否成功等操作,特此记录存档,以备后用. oracle服务启动操作命令 1.查看orac ...

  4. T-SQL 基于关系分割字符串

    今天晚上学习了下 SQL 基于关系的运算,同时也捉摸着写了个例子,虽然我知道性能不是很好,还有待优化.直接上源代码吧,思路表达出来有点困难,直接贴上代码,如果有人不懂得可以MM 我. declare ...

  5. C# 复习(1) 委托与事件

    委托定义顺序 1. 声明一个委托 2.定义一个委托变量 3. 委托变量的初始化或者给委托变量绑定一个方法 4.调用委托 事件:事件是对委托的封装. 事件只能在创建事件的类的内部调用. public c ...

  6. C#彻底解决Web Browser 跨域读取Iframes内容

    C#彻底解决Web Browser 跨域读取Iframes内容 用C# winform的控件web browser 读取网页内容,分析一下数据,做一些采集工作. 如果是同一个域名下面还是好办的,基本上 ...

  7. 移动WEB开发资源

    很多移动开发的资源 http://www.cnblogs.com/PeunZhang/p/3407453.html

  8. TCP和UDP

    目录: TCP流式协议 TCP模板 TCP聊天室 TCP通信与连接循环 TCP粘包 socketserver实现并发 UDP数据报协议 UDP模板 UDP传输 socketserver实现并发 TCP ...

  9. appium:运行脚本时,报404的解决办法

    对于报404的错,不要怀疑,在环境正常的情况下,一定是你的端口被占用了. 就用:查看端口:netstat -aon|findstr 5037 查看进程:tasklist /fi "PID e ...

  10. Python变量与赋值

    Python是一门独特的语言,与C语言有很大区别,初学Python很多萌新表示对变量与赋值不理解,学过C的都知道,给变量赋值时,需要先指定数据类型,同时会开辟一块内存区域,用于存储值,例如: int ...