mysql核心知识之外键约束剖析

最新推荐文章于 2022-10-15 14:38:42 发布
最新推荐文章于 2022-10-15 14:38:42 发布
阅读量204 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43432438/article/details/124943421
版权

什么是外键？

外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。

添加外键约束：

语法：foreign key (字段名) references 关联的表名(关联表的字段名)
注意：主键跟外键的字段类型一定要相

create table的方法：
mysql> CREATE TABLE `employees` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
foreign key (deptnu) references dept (deptnu),
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`),
  KEY `deptnu` (`deptnu`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
alter table的方法：
mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> alter table employee add foreign key (deptnu) references dept (deptnu);
Query OK, 14 rows affected (0.06 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> show create table employee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`),
  KEY `deptnu` (`deptnu`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

添加外键约束后效果：

添加约束的表时‘孙子’，指向的表是’大爷‘；
如果添加外键约束的表插入的数据中，添加了外键约束的字段的数据 在指向表中的字段中不存在该数据，则添加失败。

如下图dept表中deptnu字段不存在50的数据，employee表的deptnu字段添加了外键约束，插入数据50,显示失败。

例子：

mysql> insert into employee values('10325','xiaohua','销售员','10010','2022-05-24','6000','50');
1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`))
mysql> select * from dept;
+--------+--------+------+
| deptnu | dname  | addr |
+--------+--------+------+
|     10 | 研发部 | 北京 |
|     20 | 工程部 | 上海 |
|     30 | 销售部 | 广州 |
|     40 | 财务部 | 深圳 |
+--------+--------+------+
4 rows in set (0.04 sec)

mysql> insert into dept values('50','测试部门','南充');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values('10325','xiaohua','销售员','10010','2022-05-24','6000','50');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+-------+---------+--------+-------+------------+----------+--------+
| empno | ename   | job    | mgr   | hiredate   | sal      | deptnu |
+-------+---------+--------+-------+------------+----------+--------+
|  1001 | 小乔    | 文员   |  1013 | 2018-12-17 | 8000.00  |     20 |
|  1002 | 牛魔王  | 销售员 |  1006 | 2018-02-20 | 16000.00 |     30 |
|  1003 | 程咬金  | 销售员 |  1006 | 2017-02-22 | 12500.00 |     30 |
|  1004 | 猪八戒  | 经理   |  1009 | 2001-04-02 | 29650.00 |     20 |
|  1005 | 后裔    | 销售员 |  1006 | 2011-09-28 | 12500.00 |     30 |
|  1006 | 猴子    | 经理   |  1009 | 2011-05-01 | 28500.00 |     30 |
|  1007 | 张飞    | 经理   |  1009 | 2011-09-01 | 24500.00 |     10 |
|  1008 | 诸葛亮  | 分析师 |  1004 | 2017-04-19 | 30000.00 |     20 |
|  1009 | 唐僧    | 董事长 | NULL  | 2010-11-17 | 50000.00 |     10 |
|  1010 | 韩信    | 销售员 |  1006 | 2018-09-08 | 15000.00 |     30 |
|  1011 | 妲己    | 文员   |  1008 | 2018-05-23 | 11000.00 |     20 |
|  1012 | 安琪拉  | 文员   |  1006 | 2011-12-03 | 9500.00  |     30 |
|  1013 | 林俊杰  | 分析师 |  1004 | 2011-12-03 | 30000.00 |     20 |
|  1014 | 甄姬    | 文员   |  1007 | 2019-01-23 | 7500.00  |     10 |
| 10325 | xiaohua | 销售员 | 10010 | 2022-05-24 | 6000.00  |     50 |
+-------+---------+--------+-------+------------+----------+--------+
15 rows in set (0.06 sec)

删除外键约束：

注意：在干掉外键索引之前必须先把外键约束删除，才能删除索引

mysql> alter table employee drop index deptnu;
1553 - Cannot drop index 'deptnu': needed in a foreign key constraint

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

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

mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

注意点总结：

（1）俩个表，主键跟外键的字段类型一定要相同
​
（2）要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的
​

mysql> alter table employees engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employees;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> alter table employees add foreign key (deptnu) references dept (deptnu);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into employees values ('10020','xiaohuang','测试员','10000','2022-05-24','8000','60');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employees;
+-------+-----------+--------+-------+------------+---------+--------+
| empno | ename     | job    | mgr   | hiredate   | sal     | deptnu |
+-------+-----------+--------+-------+------------+---------+--------+
| 10020 | xiaohuang | 测试员 | 10000 | 2022-05-24 | 8000.00 |     60 |
+-------+-----------+--------+-------+------------+---------+--------+
1 row in set (0.05 sec)

mysql> select * from dept;
+--------+----------+------+
| deptnu | dname    | addr |
+--------+----------+------+
|     10 | 研发部   | 北京 |
|     20 | 工程部   | 上海 |
|     30 | 销售部   | 广州 |
|     40 | 财务部   | 深圳 |
|     50 | 测试部门 | 南充 |
+--------+----------+------+
5 rows in set (0.04 sec)

（3）在干掉外键索引之前必须先把外键约束删除，才能删除索引

爱卿呀
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
千锋重庆Java小编浅谈MySQL外键约束的问题
qf2019的博客
08-24 159
外键约束是为了保持子表与父表的数据一致，用于在两个表之间建立关系，需要指定引用主表的哪一列。InnoDB是MySQL内置存储引擎中唯一一个支持外键的。InnoDB会强制外键使用索引。事实上，我们每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。本文我们就来探讨一下MySQL外键约束的问题。 首先我们明确一点，外键约束是一种约束，这个约束的存在，会保证表间数据的关系“始终完整”。因此，外键约束的存在，并非全然没有优点。比如使用外键，可以保证数据的完整性和一致性
MySQL主主复制 外键_MySQL组复制MGR（四）-- 单主模式与多主模式
weixin_35879114的博客
02-18 131
(一)概述组复制可以运行在单主模式下，也可以运行在多主模式下，默认为单主模式。组的不同成员不能部署在不同模式下，要切换模式，需要使用不同配置重新启动组而不是单个server。相关参数如下：# 该参数决定启用单主模式(on)还是多主模式(off)，默认单主模式loose-group_replication_single_primary_mode=off# 在多主模式下部署时，将检查语句以确保它们与该...
【MySQL】MGR集群相关简介
weixin_33912445的博客
01-28 268
1.MGR简介 MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供。MGR基于分布式paxos协议，实现组复制，保证数据一致性。内置故障检测和自动选主功能，只要不是集群中的大多数节点都宕机，就可以继续正常工作。提供单主模式与多主模式，多主模式支持多点写入。 2.原理简单介绍 组复制是一种可用于实现容...
【MySQL篇】MySQL数据、约束类型以及字符集
debug
10-15 293
MySQL数据、约束类型以及字符集
Column 'id' cannot be null
热门推荐
清欢
09-16 1万+
异常信息 16-Sep-2019 17:14:55.384 涓ラ噸 [http-nio-8080-exec-6] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [SpringMVC] in context with path [] threw exception [Request...
mysql 核心知识要点
02-25
MySQL 核心知识要点 MySQL 是一种关系型数据库管理系统，提供了丰富的知识点和应用场景。本文将从 MySQL 的基本操作和使用、优化、部署、数据库基本介绍、关系型数据库、非关系型数据库、MySQL 服务器、数据库对象...
MySQL笔记.docx
03-16
MySQL是一个广泛使用的开源关系型数据库管理系统（RDBMS），它...了解和掌握MySQL的基本概念、SQL语法及最佳实践，对于从事IT行业的人员来说至关重要，无论是在开发、运维还是数据分析等领域，MySQL都能发挥重要作用。
mysql基础知识总结（第二版）
12-06
MySQL是世界上最受欢迎的关系型数据库...总的来说，掌握这些MySQL基础知识对于数据库管理、数据分析和应用开发至关重要。熟悉这些概念和操作，能够帮助你更有效地管理数据库，优化查询性能，并确保数据的安全和一致性。
169集全新MySQL课程 MySQL技能全面探索 MySQL核心特训教程 MySQL零基础实战班视频
05-05
全新的MySQL视频教程，课程对MySQL的核心知识点进行了大量深入的展开，细致的讲解和总结 较以往的MySQL核心基础课程有更好的优势，是MySQL运维人员 编程人员及相关技术人员的必备学习课程 ├─10、课程：进阶...
MySQL必知必会系列.pdf
03-11
服务器是 MySQL 的核心部分，负责处理客户端的请求，管理数据库、表、记录等。客户端是与服务器交互的接口，负责将用户的请求传递给服务器。数据库是 MySQL 中的基本存储单元，一个数据库可以包含多个表。表是数据库...
MySQL表的约束(基础篇)
欢迎来到我的博客，在这里我会用我的方式来讲解知识，请大家多多关照
07-14 729
目录约束类型自增主键分布式引出小问题约束类型都是根据这个表格来叙述的: NOT NULL NULL :->指定某列可不可以为null ,约束类型 NOT NULL没有约束情况下 ->指定列可以为NULL-->可以插入NULL 有约束的情况下 此时表格中NULL变为了NO ,也就是必填项,当我们再次插入NULL,就会失败因为进行了约束.unique 保证某列的每一行不能重复,比如当你插入 id =1 是再次插入1就会插入失败 有约束情况: 当你再次插...
MySQL之MGR原理+保姆级配置部署
weixin_44508518的博客
09-12 3055
如上图所示，由3个节点组成一个复制组，Consensus层为一致 性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务 才能够最终得以提交并响应。**高灵活性：**提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主 节点进行，多主模式支持多节点写入。**高扩展性：**节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增 量数据，直到与其他节点数据一致；**高一致性：**基于分布式paxos协议实现组复制，保证数据一致性；
mysql数据库外键怎么设置_MySql外键设置详解
weixin_36003021的博客
01-18 5386
外键的作用,主要有两个:一个是让数据库自己通过外键来保证数据的完整性和一致性一个就是能够增加ER图的可读性有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于没有通过外键的检测而使得开发人员删除,插入操作失败.他们觉得这样很麻烦其实这正式外键在强制你保证数据的完整性和一致性.这是好事儿.例如：有一个基础数据表，用来记录商品的所有信息。其他表都保存商品ID。查询时需要连表来...
浅谈 MySQL的外键的作用
JackLiu16的博客
06-19 1万+
MySQL中外键的介绍： MySQL外键必须使用存储引擎为  innDB  其中MySAM 和MEMORYH这两种引擎不支持 由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。 设置外键约束的两个表之间会具有父子关系，即子表中外键的字段的取值范围由父表所决定 设置...
mgr未同步 mysql_【mysql】Mgr实现数据库高可用架构
weixin_39820588的博客
12-18 149
MGR简介MySQL Group Replication(下简称：MGR)是MySQL官方推出的一种基于Paxos协议的状态机复制。在MGR出现之前，用户常见的MySQL高可用方式，无论怎么变化架构，本质就是Master-Slave架构。MySQL 5.7版本开始支持无损半同步复制(lossless semi-sync replication)，从而进一步提示数据复制的强一致性。MGR与其他复制的...
外键约束详解及术语释疑
cnds123的专栏
10-06 1万+
外键约束详解 先要理清几个概念： 1）候选键（CandidateKey）：关系中的一个属性组，其值能唯一标识一个元组，若从该属性组中去掉任何一个属性，它就不具有这一性质了，这样的属性组称作候选键（候选码）。 2）主键（PrimaryKey）：当有多个候选键时，可以选定一个作为主键，选定的候选键称为主键（主码）。 主键唯一标识表中的每行记录。主键约束有如下特点：每个表...
mysql唯一键约束靠唯一索引来约束
最新发布
04-30
MySQL唯一键约束是一项非常重要的数据库约束，它用于确保表中的某个列不包含重复的值。为了实现唯一键约束，MySQL引入了唯一索引的概念。 唯一索引是一种特殊的索引，它要求表中的每个索引值都具有唯一性，即一个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值