-
2022-03-20 00:59:47
外键约束(FOREIGN KEY(字段) REFERENCES 主表名(字段))
外键约束的作用:
限定某个字段的引用完整性
- 我们之前在讲数据完整性的时候就提到过引用完整性
- 引用完整性(eg: 员工所在的部门,在部门表中一定要能找到这个部门)
主表和从表:
主表也称之为父表,从表也称之为子表
- 主表(父表) : 被引用的表
- 从表(子表) : 引用别人的表
- 我们如何区分从表和主表?
- 我们只需要记住: 我们在"从表"中指定外键约束,那么我们就可以知道那个表是从表了,知道了哪个表是从表,那么我们也就知道了哪个表是主表了
外键约束的特点:
-
从表的外键列,必须引用主表的主键列或者唯一约束的列
- 因为被参考的值(也就是被引用的值)必须是唯一的
-
在创建外键约束的时候,如果不给外键约束命名,默认的外键约束名不是列名,而是自动产生的一个外键名(例如: student_ibfk_1;)当然我们也可以给外键约束命名
-
在创建表时就指定外键约束的话,一定要先创建主表再创建从表
- 因为如果我们是在创建表的时候创建外键约束,这个时候如果我们直接创建从表,那么由于我们还没有创建主表,这个时候我们却要在从表中引用主表,那么当然执行就会出现错误
-
删除表时,要先删除从表(子表),再删除主表(父表)
- 又或者将外键约束先删除掉,约束没有了,那么怎么删都无所谓了
-
当主表的记录被从表参照时,主表的记录将不允许被删除,如果我们要删除数据,则要先删除从表中依赖该记录的数据,然后才可以删除主表中的数据
-
当"从表"中指定外键约束,并且一个表中可以有多个外键约束
-
从表中的外键列与主表被参照的列的列名可以不相同,但是数据类型一定是相同的,逻辑意义一致,如果类型不一样,创建子表时就会出现错误
-
当创建外键约束时,系统默认会在所在列上建立对应的普通索引,索引名是外键的约束名
-
删除外键约束后,必须手动删除对应的普通索引
那么我们如何添加外键约束?
这里添加外键约束我们还是分为两种方式
方式一: 在CREATE TABLE时添加约束
- 注意:我们在添加外键约束的时候一般都是将外键约束设置为表级约束,因为以列级约束添加约束的时候不能指定约束名,而我们对于外键约束来讲默认的约束名不是列名,又由于我们删除外键约束的时候要指明外键约束名,因为我们的一个表中可以声明多个外键约束,所以我们就要通过外键约束名来删除外键约束,所以我们最好在创建外键约束的时候最好就指明外键约束名
这里我们通过举例说明如何在CREATE TABLE时添加约束
我们先创建主表:(如下)
CREATE TABLE dept1( dept_id INT PRIMARY KEY, dept_name VARCHAR(15) );
- 这里我们就是创建了主表(部门表)
然后这里我们创建从表:(如下)
CREATE TABLE emp1( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(15), department_id INT, #表级外键约束 CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id) );
- 这里我们创建了从表(员工表)
- 这里使用员工表中的department_id字段和部门表中的dept_id字段建立了外键连接,这个时候部门表就是主表,员工表就是从表
- 这个时候要注意: 主表中被引用的字段是键列(也就是有唯一性约束或者主键约束)
- 这里我们添加外键约束的时候通过了表级约束的方式,并使用了constraint关键字对外键约束命名为 fk_emp1_dept_id
- references关键字的作用就是标示主表中被引用的列
那么有了外键约束之后有什么作用?
这里我们通过举例说明
INSERT INTO emp1 VALUES(1001,'tom',10);
- 这个时候会添加失败,因为这个时候我们的主表(部门表)中还没有添加部门,那么在从表中添加数据就是添加不进去的 — 因为我们的外键约束的功能就是判断在从表中添加的数据中的外键约束字段的值一定要在主表中被引用的字段的值中找到相同值,否则就判断添加不成功
所以说我们要想在从表(员工表)中添加数据,那么就先要在主表(部门表)中添加数据,先要有部门,那么才能有这个部门的员工
INSERT INTO dept1 VALUES(10,'IT');
- 这个时候我们就在部门表(主表)中添加了一条记录,也即是添加了10号部门,那么后面我们就可以在员工表中添加数据了,但是注意要添加的员工一定部门id都要为10
这个时候我们再执行一次添加员工的操作
INSERT INTO emp1 VALUES(1001,'tom',10);
然后这个时候我们从表(员工表)中有10号部门的员工了,那么接下来我们就不能删除主表中的id为10的部门了
DELETE FROM dept1 WHERE dept_id = 10;
- 这个时候就会删除失败,因为我们从表中有10号部门的员工了,这个时候在主表中就不能删除10号部门了,除非先将从表中10号部门的员工删除掉,或者将外键约束删除掉
方式二 : 在ALTER TABLE时添加外键约束
首先我们先创建两个表
- 这里我们先创建一个部门表(主表)
CREATE TABLE dept2( dept_id INT PRIMARY KEY, dept_name VARCHAR(15) );
- 然后这里我们再创建一个员工表(从表)
CREATE TABLE emp2( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(15), department_id INT );
- 然后这里我们在ALTER TABLE时添加外键约束
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);
- 这里我们就是在员工表中使用员工表中的department_id字段和部门表中的dept_id建立了外键连接(我们的部门表中的dept_id是一个主键列(也就是添加了主键约束)),所以这里外键约束添加也会是成功的
建立了外键约束之后我们可以通过一条指定的SQL语句来查询外键约束是否添加成功
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_name = 'emp2';
- 注意: 这里的 where table_name = ‘emp2’中的’'是一对单引号,表示是字符串,也就是表名,这里时为了判断表名,显然我们就是要对字符串之间进行判断,而不是加着重号和关键字进行区分
我们还要将一件事情: 外键约束等级
-
Cascade : 在父表上update/delete记录时,同步的update/delete子表中的匹配的记录
-
Set null : 在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能设置为not null
- 如果子表外键列设置为not null,那么我们就不能使用Set null的方式,因为这个时候是冲突的,按理我们设置了Set null之后如果我们要修改或者删除主表中的记录的时候就会将从表中的对应的记录的外键列设置为null,但是这个时候我们的从表中的外键列又设置了not null,这样肯定是不行,所以当外键约束等级为Set null时,这个时候就不能将从表中的外键列设置为not null
-
No action : 如果在子表中有和主表匹配的记录,那么就不允许对主表进行update/delete操作
-
Restrict : 和No action是一样的
-
Set default : 这个我们知道就行,不使用
注意: 如果没有指定外键约束等级,就相当于Restrict方式(或者说是No action的方式)
- Restrict方式和No action方式功能是一样的
这里我们通过一个例子来理解如果给外键约束指定约束等级
eg: 这里我们设置为on update on delete setnull
- 我们也还是先创建主表(部门表)
CREATE TABLE dept3( d_id INT PRIMARY KEY, d_name VARCHAR(50) );
- 然后紧接着创建从表(员工表)
CREATE TABLE emp3( e_id INT PRIMARY KEY, e_name VARCHAR(15), dept_id INT , CONSTRAINT fk_emp3_dept_id FOREIGN KEY(dept_id) REFERENCES dept3(d_id) ON UPDATE CASCADE ON DELETE SET NULL );
- 这里我们就是以表级约束的方式为从表中的dept_id字段和主表中的d_id字段建立了外键约束,并且设置了外键约束等级为: on update cascade on delete set null, 也就是对于update操作,我们的外键约束等级是cascade(也就是同步修改),对于delete操作我们的外键约束等级是set null(也就是主表删除之后,从表对应字段设置为null)
- 这里我们可以发现,我们的外键约束等级是在设置外键约束时给定,通过 : on update 约束等级 on delete 约束等级 的方式
注意:对于外键约束,我们最好采用ON UPDATE CASCADE ON DELETE RESTRIT的方式(也就是修改主表,那么从表跟着修改,但是不允许删除主表中的被外键约束的字段当我们从表中又对应数据时)
那么我们如何删除外键约束?
我们删除外键约束之后要手动的删除外键约束对应的普通索引
我们先删除外键约束
ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept_id;
- 这里就已经将我们的emp1表中的名为fk_emp_dept_id的外键约束删除了
而在删除了外键约束之后我们要删除这个外键约束对应的普通索引
ALTER TABLE emp1 DROP INDEX fk_emp1_dept_id;
- 注意: 外键约束对应的普通索引的索引名和外键约束名相同
更多相关内容 - 我们之前在讲数据完整性的时候就提到过引用完整性
-
MySQL外键约束的实例讲解
2020-12-14 06:44:31MySQL的外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化。从这个特点来看,它主要是为了保证表数据的一致性和完整性的。 对于两个通过外键关联的表,相关联字段中主键所在的表是主... -
MySQL删除有外键约束的表数据方法介绍
2020-12-16 10:06:42在MySQL中删除一张表或一条数据的时候,...禁用外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=0; 然后再删除数据 启动外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=1; 查看当前FOREIGN_KEY_CHECKS的值,可用 -
MYSQL 数据库给表加外键约束条件 (史上最详细教程)
2020-12-14 10:57:31(还未加外键约束) 1. mysql> show columns from message;//信息表 +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------... -
详解MySQL 外键约束
2020-12-14 08:20:16MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 2.外键的使用条件 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持) 外键列必须建立了索引,MySQL ... -
总结三种MySQL外键约束方式
2020-12-14 22:07:21外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。 这里以MySQL为例,总结一下3种外键约束方式的区别和联系。 ... -
MySQL删除表的时候忽略外键约束的简单实现
2020-12-16 14:16:18删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心。但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情;另外在测试的时候,也有需要重新创建数据库... -
聊聊Oracle外键约束的几个操作选项
2021-01-19 22:05:50外键约束的作用,是保证字表某个字段取值全都与另一个数据表主键字段相对应。也是说,只要外键约束存在并有效,不允许无参照取值出现在字表列中。具体在Oracle数据库中,外键约束还是存在一些操作选项的。本篇主要从... -
mysql数据库触发器,外键约束模式知识点
2017-07-06 20:55:52内包含外键约束模式,数据库的视图基本操作 -
详解MySQL中的外键约束问题
2021-01-19 22:29:23使用MySQL开发过数据库驱动的小型web应用程序的人都知道,对关系数据库的表进行创建、检索、更新和删除等操作都...MySQL外键约束条件 MySQL的外键约束条件有以下几种: · CASCADE : 从父表删除或更新行时自动删除 -
oracle查看主外键约束关系
2015-09-21 15:46:46oracle查看主外键约束关系,根据此SQL可以进行主外键约束的查询! -
django在开发中取消外键约束的实现
2020-09-16 20:31:06主要介绍了django在开发中取消外键约束的实现,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧 -
数据库下所有表的外键约束查询
2015-08-20 15:21:34用于查询某个数据库下所有表的外键约束情况,以便统一分析外键约束是否合理;主要查询出外键的,是否级联删除,级联更新,强制外键约束,强制复制约束始终状态。如想使用别的状态,请自行添加。下载的童鞋别下载错了... -
外键约束
2020-11-07 21:40:33外键约束 只有InnoDB存储引擎支持外键,并且在创建外键的时候,我们子表的外键必须关联主表的主键。 外键字段依赖于已经存在的表的一个主键,所以一定要先有主表。 我们的外键字段必须和我们主表的主键字段的数据...外键约束
- 只有InnoDB存储引擎支持外键,并且在创建外键的时候,我们子表的外键必须关联主表的主键。
- 外键字段依赖于已经存在的表的一个主键,所以一定要先有主表。
- 我们的外键字段必须和我们主表的主键字段的数据类型要相似:如果是数值型,两者必须类型一致,并且同时为有无符号;如果是字符串型,两者的长度可以不同,但是类型必须相同。
- 如果子表的外键字段没有创建索引,但是当我们创建完外键之后,它会自动的添加索引。
- 如果子表中有记录外键关联着主表中的记录,那么主表中的这条记录以及主表是无法删除的,子表中的这些记录可以删除。
创建外键
建表时指定外键
[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
外键名称一般以 从表字段名称_fk_主表名称 进行命名
动态删除外键
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;
动态增加外键
ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
注意:动态添加外键之前表中的记录一定是合法的记录,没有脏值,否则外键添加不成功。
外键约束的参照操作
CASCADE
级联的。当我们对父表进行删除或者更新,那么我们子表对应得也会进行删除或者是更新。
SET NULL
从父表删除或者更新记录,会设置子表对应的外键列为NULL。但是得确保这个字段是可以为空得,不然无法写入NULL值。
NO ACTION | RESTRICT
这是默认的。拒绝对父表做更新或删除操作
测试
建表时指定外键测试
-- 创建新闻分类表 CREATE TABLE IF NOT EXISTS news_cate( id TINYINT KEY AUTO_INCREMENT COMMENT '编号', cateName VARCHAR(50) NOT NULL COMMENT '分类名称', cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述' ); INSERT news_cate(cateName) VALUES('国内新闻'), ('国际新闻'), ('娱乐新闻'), ('体育新闻'); -- 创建新闻表 CREATE TABLE IF NOT EXISTS news( id INT KEY AUTO_INCREMENT COMMENT '编号', title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题', content VARCHAR(1000) NOT NULL COMMENT '新闻内容', cateId TINYINT NOT NULL COMMENT '新闻所属分类编号', FOREIGN KEY(cateId) REFERENCES news_cate(id) ); INSERT news(title,content,cateId) VALUES('a1','aaaa1',1), ('a2','aaaa2',1), ('a3','aaaa3',4), ('a4','aaaa4',2), ('a5','aaaa5',3);
测试插入非法记录
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
也就会报错了
测试删除父表中的记录和删除父表
-- 测试删除父表中的记录 DELETE FROM news_cate WHERE id=1;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
-- 测试删除父表 DROP TABLE IF EXISTS news_cate;
3730 - Cannot drop table 'news_cate' referenced by a foreign key constraint 'news_ibfk_1' on table 'news'.
测试更新父表中的记录
UPDATE news_cate SET id=10 WHERE id=1;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
尝试向父类插入记录
INSERT news_cate(cateName) VALUES('教育新闻');
+----+----------+----------+ | id | cateName | cateDesc | +----+----------+----------+ | 1 | 国内新闻 | 无 | | 2 | 国际新闻 | 无 | | 3 | 娱乐新闻 | 无 | | 4 | 体育新闻 | 无 | | 5 | 教育新闻 | 无 | +----+----------+----------+
这条语句是可以成功的
尝试更改新插入的记录
这条记录是刚插入的,也就是子表还没有使用的。
UPDATE news_cate SET cateName='教育' WHERE id=5;
+----+----------+----------+ | id | cateName | cateDesc | +----+----------+----------+ | 1 | 国内新闻 | 无 | | 2 | 国际新闻 | 无 | | 3 | 娱乐新闻 | 无 | | 4 | 体育新闻 | 无 | | 5 | 教育 | 无 | +----+----------+----------+
尝试在建表时指定外键并指定外键名称
-- 创建新闻分类表 CREATE TABLE IF NOT EXISTS news_cate( id TINYINT KEY AUTO_INCREMENT COMMENT '编号', cateName VARCHAR(50) NOT NULL COMMENT '分类名称', cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述' ); -- 创建新闻表 CREATE TABLE IF NOT EXISTS news( id INT KEY AUTO_INCREMENT COMMENT '编号', title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题', content VARCHAR(1000) NOT NULL COMMENT '新闻内容', cateId TINYINT NOT NULL COMMENT '新闻所属分类编号', CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id) );
下面时表的信息,我们就可以看到我们命名的外键名称
| news | CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `title` varchar(100) NOT NULL COMMENT '新闻标题', `content` varchar(1000) NOT NULL COMMENT '新闻内容', `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号', PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`), KEY `cateId_fk_newsCate` (`cateId`), CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
尝试动态删除外键
ALTER TABLE news DROP FOREIGN KEY cateId_fk_newsCate;
查看表信息,我们可以发现之前的外键被删除了
| news | CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `title` varchar(100) NOT NULL COMMENT '新闻标题', `content` varchar(1000) NOT NULL COMMENT '新闻内容', `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号', PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`), KEY `cateId_fk_newsCate` (`cateId`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
尝试动态添加外键
ALTER TABLE news ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
查看表信息
| news | CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `title` varchar(100) NOT NULL COMMENT '新闻标题', `content` varchar(1000) NOT NULL COMMENT '新闻内容', `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号', PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`), KEY `cateId` (`cateId`), CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
在动态添加外键的时候指定外键名称
ALTER TABLE news ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
查看表信息
| news | CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `title` varchar(100) NOT NULL COMMENT '新闻标题', `content` varchar(1000) NOT NULL COMMENT '新闻内容', `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号', PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`), KEY `cateId_fk_newsCate` (`cateId`), CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
尝试在表中有非法记录时动态插入外键
-- 插入非法记录 INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
-- 尝试动态添加外键 ALTER TABLE news ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id); -- 会报错 1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`#sql-3d84_4b`, CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
我们将那个非法的记录删除后再进行动态添加外键
DELETE FROM news WHERE id=6; ALTER TABLE news ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
添加成功
所以在表中存在对于将要添加的外键来说是非法的记录时,将无法添加这个外键。
尝试指定级联操作
-- 指定级联操作 DELETE,UPDATE CASCADE -- 注意先把之前同名的外键删除 ALTER TABLE news ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id) ON DELETE CASCADE ON UPDATE CASCADE;
-- 尝试更新父表news_cate UPDATE news_cate SET id=11 WHERE id=1;
我们可以发现上面的操作中,我们仅仅对父表进行了更新,但是父表和子表同时进行了更新
SELECT * FROM news_cate; +----+----------+----------+ | id | cateName | cateDesc | +----+----------+----------+ | 2 | 国际新闻 | 无 | | 3 | 娱乐新闻 | 无 | | 4 | 体育新闻 | 无 | | 11 | 国内新闻 | 无 | +----+----------+----------+ SELECT * FROM news; +----+-------+---------+--------+ | id | title | content | cateId | +----+-------+---------+--------+ | 1 | a1 | aaaa1 | 11 | | 2 | a2 | aaaa2 | 11 | | 3 | a3 | aaaa3 | 4 | | 4 | a4 | aaaa4 | 2 | | 5 | a5 | aaaa5 | 3 | +----+-------+---------+--------+
-- 尝试删除父表中的记录 DELETE FROM news_cate WHERE id=2;
SELECT * FROM news_cate; +----+----------+----------+ | id | cateName | cateDesc | +----+----------+----------+ | 3 | 娱乐新闻 | 无 | | 4 | 体育新闻 | 无 | | 11 | 国内新闻 | 无 | +----+----------+----------+ SELECT * FROM news; +----+-------+---------+--------+ | id | title | content | cateId | +----+-------+---------+--------+ | 1 | a1 | aaaa1 | 11 | | 2 | a2 | aaaa2 | 11 | | 3 | a3 | aaaa3 | 4 | | 5 | a5 | aaaa5 | 3 | +----+-------+---------+--------+
为什么需要外键约束?
-- 创建新闻分类表 CREATE TABLE IF NOT EXISTS news_cate( id TINYINT KEY AUTO_INCREMENT COMMENT '编号', cateName VARCHAR(50) NOT NULL COMMENT '分类名称', cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述' ); INSERT news_cate(cateName) VALUES('国内新闻'), ('国际新闻'), ('娱乐新闻'), ('体育新闻'); -- 创建新闻表 CREATE TABLE IF NOT EXISTS news( id INT KEY AUTO_INCREMENT COMMENT '编号', title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题', content VARCHAR(1000) NOT NULL COMMENT '新闻内容', cateId TINYINT NOT NULL COMMENT '新闻所属分类编号' ); INSERT news(title,content,cateId) VALUES('a1','aaaa1',1), ('a2','aaaa2',1), ('a3','aaaa3',4), ('a4','aaaa4',2), ('a5','aaaa5',3); -- 插入一个错误数据 INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);
**问题1:**对于上面的这个错误数据,也就是脏数据,在news_cate中并没有对用的新闻种类与它的caseId对应,这显然是不合适的。
-- 查询news id,title,content -- news_cate cateName SELECT n.id,n.title,n.content,nc.cateName FROM news AS n INNER JOIN news_cate AS nc ON n.cateId=nc.id;
+----+-------+---------+----------+ | id | title | content | cateName | +----+-------+---------+----------+ | 1 | a1 | aaaa1 | 国内新闻 | | 2 | a2 | aaaa2 | 国内新闻 | | 3 | a3 | aaaa3 | 体育新闻 | | 4 | a4 | aaaa4 | 国际新闻 | | 5 | a5 | aaaa5 | 娱乐新闻 | +----+-------+---------+----------+
**问题2:**假如这个时候我们将news_cate下的国际新闻删除
DElETE FROM news_cate WHERE id=2;
但是对于news表中的新闻来说,它的caseId还是有2的,那么也就缺少了对应的caseId,这也显然是不合适的。
针对上面两个问题,我们就需要使用我们的外键约束。
-
MySQL外键约束详解
2021-12-19 08:02:01今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL外键约束详解。 一、MySQL外键约束作用 二、外键约束创建 (一)创建外键约束的条件 (二)在创建数据表时创建外键约束 (三)在创建数据表后添加外键约束 三...今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL外键约束详解。
一、MySQL外键约束作用
外键约束(Foreign Key)即数据库中两个数据表之间的某个列建立的一种联系。这种联系通常是以实际场景中含义完全相同的字段所造成的。MySQL通过外键约束的引入,可以使得数据表中的数据完整性更强,也更符合显示情况。下面,我举一个例子来说明MySQL外键约束的作用。
假如我们对大学学生成绩管理系统建立数据库,有两张表,一张表是学生表,存储了学生的学号、姓名、性别、院系等信息,还有一张表是成绩表,存储了学生学号、课程编号、考试成绩等信息。这样,这两张表之间就会通过学生学号建立外键约束。很自然的我们想到,成绩表的学生学号依赖于学生表的学生学号存在,如果一个学生毕业、或者退学,从学生表中删除时,那么他的相关成绩也就没有必要在成绩表中存在了。在没有创建外键关系之前,这两张表完全是独立存在的,我们可以强行在成绩表中插入一个不存在学生的相关成绩,也可以强行删除学生表中的一个学生,并且不管其成绩信息是否在成绩表中存在。但是,在建立外键关系后,MySQL数据库会约束上述两种行为,每次对数据进行插入或者删除时,都会检查数据完整性,使得我们的操作必须符合实际情况。二、外键约束创建
(一)创建外键约束的条件
MySQL数据库外键的创建,需要满足以下四个条件,否则会被MySQL数据库拒绝:
1、创建外键的表和列存在
2、组成外键的列存在索引
3、必须指定数据表的引擎为InnoDB
4、外键字段和关联字段,数据类型必须一致(二)在创建数据表时创建外键约束
在创建数据表时创建外键约束,只需要在创建数据表的create语句后面,使用foreign key关键字指定本表的外键字段,使用reference关键字指定关联表的关联字段,并且明确约束行为即可。
创建外键约束的SQL语句示例如下:create table student (id int(8),name varchar(20),department varchar(20) ,index (id))ENGINE=InnoDB; create table grade (Sid int(8),Cid int(10),score int,index(Sid),foreign key (Sid) references student(id) on delete restrict on update cascade)ENGINE=InnoDB;
在上述SQL语句中,on delete restrict 是指明在删除时外键会对该删除操作进行限制,而on update cascade是指名在更新时会对该更新操作进行同步。
(三)在创建数据表后添加外键约束
同样的,MySQL也支持在创建数据表后再添加外键约束。在上例中,我们先删除grade表,然后再创建grade表,现不创建外键,尝试在创建grade表后添加外键,相关SQL命令如下:
drop table grade; create table grade(Sid int(8),Cid int(10),score int); alter table grade add index(Sid); alter table grade add foreign key (Sid) references student(id) on delete restrict on update cascade;
执行结果如下:
三、外键约束功能演示
下面,我们就来测试一下外键约束的功能,首先,尝试向grade表插入一个不存在学生的成绩,发现被拒绝:
之后,尝试删除student表中存在成绩的学生,发现被拒绝:
紧接着,我们测试一下MySQL外键约束级联更新功能,发现,如果更改了student表中的数据,grade表也会跟着变动,如下所示:
原创不易,转载请说明出处:https://blog.csdn.net/weixin_40228200 -
【MySQL】外键约束
2021-12-17 10:08:11文章目录(一)外键约束(二)外键约束的功能(三)学生表和班级表(1)练习(2)两个问题(3)解决方案(4)结果展示 (一)外键约束 外键:是指表中某个字段的值依赖于另一个表中的某个字段的值(被依赖的字段...文章目录
(一)外键约束
-
外键:是指表中某个字段的值依赖于另一个表中的某个字段的值(
被依赖的字段必须要有主键约束或者唯一约束
) -
外键约束:用户实现数据库表的参照完整性。外键约束可以使两张表紧密结合起来,特别是对于删除/修改级联操作时,会保证数据的完整性。
-
子表/从表:使用外键约束的表 (学生表:学号,姓名,性别,年龄,班级号)
-
父表/主表:含有被依赖的字段的表(班级表:班级号,班级名)
注意:学生表中含有班级号
(二)外键约束的功能
- 同一个字段有着大量重复数据,使用外键约束后,修改只修改一次父表中的数据即可,节省时间;
注意:外键约束只有表级约束
(三)学生表和班级表
(1)练习
-- 创建主表(班级表) create table class_table( c_id int(4) primary key auto_increment, c_name varchar(10) ); -- 创建从表(学生表) create table stu_table( s_id int PRIMARY key auto_increment, s_name varchar(10) not null, s_sex char(1) check(s_sex = '男' or s_sex = '女'), s_age int(3) check(s_age > 0 and s_age < 100), c_id int(4) ); -- 班级表添加数据 insert into class_table values(NULL, 'xg1901'), (NULL, 'xg1902'); -- 查看班级表数据 select * from class_table; -- 学生表中插入数据 insert into stu_table values (NULL, '香菱', '女', 18, 1); insert into stu_table values (NULL, '行秋', '男', 18, 2); insert into stu_table values (NULL, '胡桃', '女', 16, 2); insert into stu_table values (NULL, '班尼特', '男', 18, 1); -- 查看学生表数据 select * from stu_table;
(2)两个问题
- 问题一:班级表中只有1, 2 ,那我们在学生表中插入3班学生呢?
insert into stu_table values (NULL, '测试名', '女', 18, 3 );
结果很明显添加成功,但这种做法就是错误的,因为根本没有c_id = 3的班
- 问题二:那我们删除班级表中的一条记录呢,那2班的人怎么办?
比如:删除xg1902
delete from class_table where c_id = 2;
那么学生表中c_id = 2的学生有没有被删除呢??很显然并没有
(3)解决方案
-
产生原因:未将外键约束语法添加进去
-
添加外键约束(
只有表级约束
):
1.创建表时添加表级外键约束
语法:constraint 约束名 foreign key 子表名 (子表字段名) references 父表名 (父表唯一字段名)
-- 创建从表(学生表) create table stu_table( s_id int PRIMARY key auto_increment, s_name varchar(10) not null, s_sex char(1) check(s_sex = '男' or s_sex = '女'), s_age int(3) check(s_age > 0 and s_age < 100), c_id int(4), -- 创建时添加表级外键约束 constraint fk_c_id foreign key (c_id) references class_table (c_id) );
2.对已创建的表添加表级外键约束
alter table 子表名 add constraint 约束名 foreign key 子表名 (子表字段名) references 父表名 (父表唯一字段名);
-- 已存在的表添加表级外键约束 alter table stu_table add constraint fk_c_id foreign key (c_id) references class_table (c_id);
(4)结果展示
(四)外键策略
(1)策略一:手动置空
(2)策略二:级联操作 on update on delete
创建约束 外键策略;
什么是级联呢?
开启级联后,更新/删除一个主表的主键值(唯一字段),系统会相应的更新/删除所有从表匹配的外键值。
先删除旧的外键约束,在添加含有外键策略(级联操作)的外键约束
- 语法格式:
-- 1.先删除旧的外键约束 alter table 从表名 drop foreign key 外键约束名; -- 2.重新添加带有级联操作(更新/删除)的外键约束 alter table 从表名 add constraint 外键约束名 foreign key (从表字段名) references 主表名 (主表字段名) on update cascade on delete cascade;
- 示例:
-- 1.先删除旧的外键约束 alter table stu_table drop foreign key fk_c_id; -- 2.重新添加带有级联操作(更新/删除)的外键约束 alter table stu_table add constraint fk_c_id foreign key (c_id) references class_table (c_id) on update cascade on delete cascade; -- 级联更新 update class_table set c_id = 1 where c_id = 3; -- 级联删除 delete from class_table where c_id = 2;
(3)级联置空 set null
举个例子,学生表中有4个2班的学生, 班级表有1班和2班,假设删除班级表中的2班,出现的情况如下:
- 外键约束的外键策略set null:学生表中的所有2班学生的班级号为null
-
-
MySQL 关闭子表的外键约束检察方法
2020-09-09 19:21:43下面小编就为大家带来一篇MySQL 关闭子表的外键约束检察方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧 -
在Oracle数据库中添加外键约束的方法详解
2020-09-10 04:54:47主要介绍了在Oracle数据库中添加外键约束的方法,需要的朋友可以参考下 -
MySQL外键约束常见操作方法示例【查看、添加、修改、删除】
2020-09-09 09:45:06主要介绍了MySQL外键约束常见操作方法,结合实例形式分析了mysql针对外键约束的查看、添加、修改、删除等相关操作实现方法,需要的朋友可以参考下 -
3.外键约束
2022-04-05 18:10:09外键约束&外键策略 -
「外键约束」外键约束 - seo实验室
2021-02-08 05:46:14外键约束如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键外键(FOREIGN KEY)假设有有A、B两张数据表,A表有一个字段id用来唯一标识A中的一条记录,B表有一个字段a_id来关联A表的一... -
MySQL数据库中的外键约束详解_数据库的主键和外键
2020-09-21 06:49:26[导读 ] 使用 MySQL 开发过数据库驱动的小型 web 应用程序的人都知道对关系数据库 的表进行创建 检索更新和删除等操作都是些比较简单的过程 理论上 只要掌握了最常 见的 SQL 语句的用法并熟悉您选择使用的服务器端... -
mysql删除外键约束
2022-05-17 19:02:511.通过数据库创建表的语句查看外键约束名 SHOW CREATE TABLE movie -- 建表语句如下 CREATE TABLE `movie` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) DEFAULT NULL, `filename` VARCHAR(255... -
SQL Sever 小技巧之解决外键约束(禁用、启用外键约束)
2021-05-30 22:23:15SQL Sever 小技巧之解决外键约束(禁用、启用外键约束) 因为有外键约束的存在,在从表中,修改主表不存在的数据是不合法的;在主表中,删除从表中已存在的主表信息也是不合法的。 当然方法总比困难多,以下两种方法各有... -
MySQL外键约束(FOREIGN KEY)案例讲解
2022-04-28 23:46:13MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。 外键用来建立主表与从表的... -
sql外键约束
2022-01-13 11:50:56sql外键约束对于已经存在的字段添加外键约束在创建数据表时设置外键约束删除外键约束 外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证,数据如果不合法则更新和插入会失败,保证数据的有效...