MySQL — 外键关联操作

举报
云物互联 发表于 2021/08/06 00:53:21 2021/08/06
【摘要】 目录 文章目录 目录MySQL 的外键约束创建表时定义外键(References,参照)修改原有表的外键约束删除外键约束参考文档 MySQL 的外键约束 注意,MySQL 的 InnoDB 表引擎才支持外键关联,MyISAM 不支持。MySQL 还支持手动打开或关闭外键约束:SET FOREIGN_KEY_CHECKS = 0/1;。 使用外键约束最...

目录

MySQL 的外键约束

注意,MySQL 的 InnoDB 表引擎才支持外键关联,MyISAM 不支持。MySQL 还支持手动打开或关闭外键约束:SET FOREIGN_KEY_CHECKS = 0/1;

使用外键约束最大的好处在于 MySQL 会帮助我们完成数据的一致性检查。当我们使用默认的外键类型 RESTRICT 时,在创建、修改或者删除记录时都会检查引用的合法性。

假设我们的数据库中包含 posts(id, author_id, content)authors(id, name) 两张表,在执行如下所示的操作时都会触发数据库对外键的检查:

  • 向 posts 表中插入数据时,检查 author_id 是否在 authors 表中存在;
  • 修改 posts 表中的数据时,检查 author_id 是否在 authors 表中存在;
  • 删除 authors 表中的数据时,检查 posts 中是否存在引用当前记录的外键;

作为专门用于管理数据的系统,数据库与应用服务相比能够更好地保证完整性,而上述的这些操作都是引入外键带来的额外工作,不过这也是数据库保证数据完整性的必要代价。上述的这些分析都是理论上的定性分析,我们其实可以简单的定量分析一下引入外键对性能的影响。

创建表时定义外键(References,参照)

在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键,具体的语法格式如下:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]

  
 
  • 1

示例:

# 部门表 tb_dept1(主表)
CREATE TABLE tb_dept1
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	location VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

# 员工表 tb_emp6(从表),创建外键约束,让 deptId 作为外键关联到 tb_dept1 的主键 id。
CREATE TABLE tb_emp6
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT,
	CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

NOTE:从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。

以上语句执行成功之后,在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束,外键名称为 deptId,其依赖于表 tb_dept1 的主键 id。

查看主表的约束信息:

MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='tb_dept1'\G;
*************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: test_db CONSTRAINT_NAME: fk_emp_dept1 TABLE_CATALOG: def TABLE_SCHEMA: test_db TABLE_NAME: tb_emp6 COLUMN_NAME: deptId ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: test_db REFERENCED_TABLE_NAME: tb_dept1 REFERENCED_COLUMN_NAME: id
1 row in set (0.00 sec)

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

修改原有表的外键约束

外键约束也可以在修改表时添加,但是添加外键约束的前提是:从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下:

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

  
 
  • 1

示例:修改数据表 tb_emp2,将字段 deptId 设置为外键,与数据表 tb_dept1 的主键 id 进行关联。

# 创建 tb_emp2(从表)
CREATE TABLE tb_emp2
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO   | PRI | NULL | |
| name   | varchar(25) | YES  | | NULL | |
| deptId | int(11) | YES  | | NULL | |
| salary | float | YES  | | NULL | |
+--------+-------------+------+-----+---------+-------+

# 添加外键约束
ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id);

MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO   | PRI | NULL | |
| name   | varchar(25) | YES  | | NULL | |
| deptId | int(11) | YES  | MUL | NULL | |
| salary | float | YES  | | NULL | |
+--------+-------------+------+-----+---------+-------+

MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row *************************** Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

删除外键约束

当一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

  
 
  • 1

示例:删除数据表 tb_emp2 中的外键约束 fk_tb_dept1。

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;

MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row *************************** Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

参考文档

http://c.biancheng.net/view/2441.html

文章来源: is-cloud.blog.csdn.net,作者:范桂飓,版权归原作者所有,如需转载,请联系作者。

原文链接:is-cloud.blog.csdn.net/article/details/108955686

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。