AI 助理
备案控制台
开发者社区 数据库 文章 正文

数据库三大范式、ER图与实体类、外键约束与级联操作你了解多少？(下）

2023-06-10 442
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 数据库三大范式、ER图与实体类、外键约束与级联操作你了解多少？
建立学生表模型

表名：student

字段名 数据类型 字段描述
s_id int 主键，自动增长
s_name varchar（20） 学生姓名
s_code varchar(20) 学号

点击对应的表图标，将之拖入到主体图纸上。


f185f7655cca12a39b5fc5cf3b866420.png


双击表头“MySQL Table”，可以编辑表名。


04b1424917036c1ac8117389128979fa.png


在字段上点击右键，选“列修改”，可以设置列属性。


0{UUGFSL$M6U1AHY1]]6ON.png


改成以下样子


点击"应用"。


编辑完成后

38486c03a0cf33c367c964bd50df733c.png



在其上点击右键，选择“选中sql”，可以看到弹出窗口自动生成建表语句

CREATE TABLE IF NOT EXISTS student (  s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_name VARCHAR(20) NOT NULL default  ‘’,
  s_code VARCHAR(20) NOT NULL default  ‘’
);
建立学生辅助信息表模型

表名：studentAdd

字段名 数据类型 字段描述
a_id int 主键，自动增长
s_id int 学生id
s_address varchar(60) 学生住址

仿照上面流程，完成此表的模型。

然后点击“1对1关系”图标。

将两个端点分别拖动到student.s_id和studentAdd.s_id上去。

点击 数据库 -> MySQL -> 全部SQL 就可以看到完整的sql建表语句

CREATE TABLE IF NOT EXISTS student (  s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_name VARCHAR(20) NOT NULL default  ‘’,
  s_code VARCHAR(20) NOT NULL default  ‘’
);
CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INT(10) UNSIGNED NOT NULL default  0,
  s_address VARCHAR(60) NOT NULL default  ‘’
);
alter table studentAdd add constraint studentAdd_s_id_fk0  foreign key (s_id) references student (s_id);

在这里针对1对1关系，建立了外键关联。

建立班级信息表模型

表名：class

字段名 数据类型 字段描述
c_id int 主键，自动增长
c_name varchar(60) 班级名称

表名：classStudent

字段名 数据类型 字段描述
cs_id int 主键，自动增长
c_id int 班级ID
s_id int 学生ID

仿照上面流程，完成此表的模型。

并建立class.c_id 与 classStudent.c_id 的1对多关联，及student.s_id 与 classStudent.s_id的关联

再看完整的建表sql

CREATE TABLE IF NOT EXISTS student (  s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_name VARCHAR(20) NOT NULL default  ‘’,
  s_code VARCHAR(20) NOT NULL default  ‘’
);
CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INT(10) UNSIGNED NOT NULL default  0,
  s_address VARCHAR(60) NOT NULL default  ‘’
);
alter table studentAdd add constraint studentAdd_s_id_fk0  foreign key (s_id) references student (s_id);
CREATE TABLE IF NOT EXISTS class (  c_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c_name VARCHAR(20) NOT NULL default  ''
);
CREATE TABLE IF NOT EXISTS classStudent ( cs_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c_id INT(10) NOT NULL,
  s_id INT(10) NOT NULL
);
alter table classStudent add constraint classStudent_c_id_fk0  foreign key (c_id) references class (c_id);
alter table classStudent add constraint classStudent_s_id_fk0  foreign key (s_id) references student (s_id);
建立课程班级信息表模型

表名：course

字段名 数据类型 字段描述
co_id int 主键，自动增长
co_name varchar(60) 课程名称

表名：courseClass

字段名 数据类型 字段描述
cc_id int 主键，自动增长
co_id int 课程ID
c_id int 班级ID

仿照上面流程，完成此表的模型。

并建立course.co_id 与 courseClass.co_id 的1对多关联，及class.c_id 与 courseClass.c_id的关联。

导出完整建表sql
CREATE TABLE IF NOT EXISTS student (  s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_name VARCHAR(20) NOT NULL default  ‘’,
  s_code VARCHAR(20) NOT NULL default  ‘’
);
CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INT(10) UNSIGNED NOT NULL default  0,
  s_address VARCHAR(60) NOT NULL default  ‘’
);
alter table studentAdd add constraint studcentAdd_s_id_fk0  foreign key (s_id) references student (s_id);
CREATE TABLE IF NOT EXISTS class (  c_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c_name VARCHAR(20) NOT NULL default  ''
);
CREATE TABLE IF NOT EXISTS classStudent ( cs_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c_id INT(10) UNSIGNED NOT NULL,
  s_id INT(10) UNSIGNED NOT NULL
);
alter table classStudent add constraint classStudent_c_id_fk0  foreign key (c_id) references class (c_id);
alter table classStudent add constraint classStudent_s_id_fk0  foreign key (s_id) references student (s_id);
CREATE TABLE IF NOT EXISTS course ( co_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  co_name VARCHAR(20) NOT NULL default  ''
);
CREATE TABLE IF NOT EXISTS courseClass (  cc_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  co_id INT(20) UNSIGNED NOT NULL default  '',
  c_id INT(20) UNSIGNED NOT NULL default  ''
);
alter table courseClass add constraint courseClass_co_id_fk0  foreign key (co_id) references course (co_id);
alter table courseClass add constraint courseClass_c_id_fk0  foreign key (c_id) references class (c_id);

3. 外键约束与级联操作🍒

外键约束（FOREIGN KEY）用来在两个表的数据之间建立连接，它可以是一列或者多列，一个表可以有一个或者多个外键。


**外键是表的一个字段，不是本表的主键，但对应另一个表的主键。**定义外键后，不允许删除另一个表中具有关联关系的行。


外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。


外键的主要作用是保持数据的一致性、完整性。


例如，部门表 tb_dept 的主键是 id，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。


主表（父表）： 对于两个具有关联关系的表而言，相关联中字段中的主键所在的表就是主表。


从表（子表）： 对于两个具有关联关系的表而言，相关联字段中的外键所在的表就是子表。



0b453a9dec02cc6e3acebfa2d274aa25.png

设置MySQL外键结束的约束


定义MySQL外键的时候，需要遵守一下规则：


主表必须已经存在数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参表（顾名思义，就是自我参照的意思），这种结构称为自参完整性。

MySQL支持外键的存储引擎只有InnoDB，这里和主键有区别，注意区分。在创建外键的时候，要求主表必须有对应的索引。从表在创建外键的时候也会自动创建对应的索引。

必须为主表定义主键。

主键不能包含空值，但是允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键（唯一键、复合主键）。

外键中列的数目必须和主表的主键中列的数目相同。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

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

其中：外键名为定义的外键约束的名称，一个表中不能有相同名称的外键；字段名表示子表需要添加外健约束的字段列；主表名即被子表外键所依赖的表的名称；主键列表示主表中定义的主键列或者列组合。

drop table 成绩;
drop table 学生;
drop table 科目;
CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY,
  name varchar(10),
);
CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY,
  name varchar(10)
);
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id),
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);
show indexes from 成绩;

提示：关联指的是关系数据库中，相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误

ERROR 3780 (HY000): Referencing column '学生ID' and referenced column 'id' in foreign key constraint 's' are incompatible.

建立外键以后产生的影响

在向从表中插入数据时：

insert into 成绩 values(1, null, null, 12.3);
insert into 成绩(id, 成绩) values (2, 12.3);
insert into 成绩 values(3, 2, 3, 12.3);
Cannot add or update a child row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))
insert into 学生 values(2, 'aaa');
insert into 科目 values(3, 'bbb');

如果外键字段插入的值，在不为null时，必须在关联的主表字段中存在，才能插入成功，否则报错。

在向从表中更新数据时：

update 成绩 set 学生ID=3 where id=3;
 Cannot add or update a child row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))
 update 成绩 set 学生ID=null where id=3;

如果外键字段更新后的值，在不为null时，必须在关联的主表字段中存在时，才能更新成功，否则报错。

在向主表中更新存在已关联从表的字段数据时：

update 学生 set id=3 where id=2;
delete from 学生 where id=2;
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))

无法更新主表中，存在已关联到从表的字段数据，只能更无关联的数据。

在主表删除存在已关联从表的字段数据时：

delete from 学生 where id=2;
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))

如果存在关联到将被删除的主表记录，的从表记录时，无法删除。必须先删除从表中对应记录后，才能删除主表记录。

在删除从表记录时：

无影响

在删除主表时：

drop table 学生;
 Cannot drop table '学生' referenced by a foreign key constraint 'fk_c_s' on table '成绩'.

当外键关系还存在时，不能删除主表。必须先删除外键后，才能删除主表。


在删除从表时：无影响


在修改表时添加外键约束

在修改数据表时添加外键约束的语法规则为：

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<

我们还是来看看学生成绩表的例子。

drop table 成绩;
drop table 学生;
drop table 科目;
CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY,
  name varchar(10)
);
CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY,
  name varchar(10)
);
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2)
);
alter table 成绩 add constraint 成绩_学生ID_fk0  foreign key (学生ID) references 学生 (id);
alter table 成绩 add constraint 成绩_科目ID_fk0  foreign key (科目ID) references 科目 (id);

删除外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系，MySQL 中删除外键的语法格式如下：

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

去除外键约束后，对应创建在从表上的索引，还继续存在，除非直接drop相关索引。

级联操作

在创建外键的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。

其中RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

不允许级联操作

CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);
--或者如下
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);

进行no action设置之后，与未设置级联操作一致（no action实际是默认模式），删除和更新会报错。

级联更新

CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON UPDATE CASCADE,
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);

进行了on update cascade设置之后，update操作主表，将主表数据和从表中关联的数据字段都一并更新。

update 学生 set id=3 where id=2;
 select * from 学生;
 select * from 成绩;

级联删除

CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON delete CASCADE,
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);

进行了on delete cascade设置之后，delete操作主表，将主表数据和从表中关联的数据都一并删除。

 delete from 学生 where id=2;
 select * from 学生;
 select * from 成绩;

on update cascade 与 on delete cascade 可以连起来写。

级联设置为null

CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
  学生ID INT(10),
  科目ID INT(10),
  成绩 DECIMAL(10,2),
  CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON delete set null,
  CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);

进行了on delete set null 或 on update set null 设置之后，delete 或 update 操作主表，将主表数据和从表中关联的数据字段都一并设为null。

 delete from 学生 where id=2;
 select * from 学生;
 select * from 成绩;


文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
存储
关键词：
数据库范式
数据库外键
数据库约束
数据库图
数据库三大范式
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Cisyam
目录
相关文章
理想shi做条咸鱼
|
4月前
|
存储 数据库
数据库设计三范式
三范式设计的最终目的都是为了减少我们的工作量，所以说，尽管三范式是一种很好的指导规范，但在实际应用中，我们也不需要太局限在三范式中，更多的是应该从项目中出发，设计出合理的表结构。
理想shi做条咸鱼
65 1
gkkkkk
|
26天前
|
存储 数据库
数据库设计三范式
数据库设计三范式
gkkkkk
25 0
2的n次方_
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库基础：约束
约束是对数据库表中字段施加的规则，确保数据的正确性、有效性和完整性。主要分为非空约束、唯一约束、默认约束、主键约束和外键约束。非空约束禁止字段值为null；唯一约束确保字段值唯一，允许null值重复；默认约束设定默认值；主键约束结合非空与唯一约束，并可设为自增型；外键约束则通过关联其他表的主键，保证数据一致性。检查约束确保字段值满足特定条件。
2的n次方_
52 1
游客j4mujezz7vm7y
|
3月前
|
数据库 Python
django中数据库外键可以自定义名称吗
django中数据库外键可以自定义名称吗
游客j4mujezz7vm7y
18 0
香吧香
|
5月前
|
关系型数据库 MySQL 数据库
navicat 查看，设计并导出数据库 ER图
navicat 查看，设计并导出数据库 ER图
香吧香
804 5
pbeskoyq7ffm4
|
4月前
|
存储 算法 Java
数据库范式与设计原则
数据库范式与设计原则
pbeskoyq7ffm4
65 0
1941623231718325
|
5月前
|
存储 关系型数据库 数据库
关系型数据库设计范式：深入理解与实践
【7月更文挑战第20天】关系型数据库设计范式是数据库设计中的重要指导原则，它通过一系列规范来减少数据冗余、提高数据一致性和优化查询性能。在实际应用中，我们应该根据具体需求和数据特点，灵活选择和应用不同的范式级别，以构建高效、可靠和可扩展的数据库系统。同时，也需要注意范式设计带来的挑战和限制，根据实际情况进行权衡和调整。
1941623231718325
173 2
三分钟热度的鱼
|
5月前
|
存储 SQL 关系型数据库
MySQL设计规约问题之在数据库设计中，为什么要适当考虑反范式的表设计
MySQL设计规约问题之在数据库设计中，为什么要适当考虑反范式的表设计
三分钟热度的鱼
56 0
路卿老师
|
21天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
34 1
听风de歌
|
23天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
听风de歌
36 4

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    sql语句查询数据库表结构信息
  • 3
    备份,还原,压缩数据库
  • 4
    使用托管代码开发WINCE自带数据库
  • 5
    数据库的范式总结（待续）
  • 6
    《你不可不知的关系数据库理论》——第1章 数据库基本概念
  • 7
    Oracle数据库中的分页--rownum
  • 8
    Android 数据库ORM框架GreenDao学习心得及使用总结<二>
  • 9
    归档问题导致的数据库无法启动
  • 10
    ADO.NET——Command(执行SQL) &amp; DataReader(读取数据库)
  • 1
    新一代数据库技术：融合人工智能与分布式系统的未来前景
    346
  • 2
    SQLite数据库介绍以及安装
    96
  • 3
    sprinboot+vue集成neo4j图数据库
    164
  • 4
    neo4j图数据库下载安装配置
    151
  • 5
    几种经典的支持数据库的表单设计器参考
    35
  • 6
    springboot数据库回滚失败原因
    53
  • 7
    spark用于分析数据并将数据保存到数据库中是
    73
  • 8
    生成数据库结构文档
    38
  • 9
    【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
    93
  • 10
    数据库SQL语言实战（六）
    49

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • DTCC 2022大会集锦《云原生一站式数据库技术与实践》
  • 阿里云瑶池数据库精要2022版
  • 2022 DTCC-阿里云一站式数据库上云最佳实践

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 快速体验PolarDB开源数据库
  • SQLServer数据库的创建
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月