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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 数据库三大范式、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 成绩;


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4月前
|
存储 数据库
数据库设计三范式
三范式设计的最终目的都是为了减少我们的工作量,所以说,尽管三范式是一种很好的指导规范,但在实际应用中,我们也不需要太局限在三范式中,更多的是应该从项目中出发,设计出合理的表结构。
|
1月前
|
存储 数据库
数据库设计三范式
数据库设计三范式
28 0
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库基础:约束
约束是对数据库表中字段施加的规则,确保数据的正确性、有效性和完整性。主要分为非空约束、唯一约束、默认约束、主键约束和外键约束。非空约束禁止字段值为null;唯一约束确保字段值唯一,允许null值重复;默认约束设定默认值;主键约束结合非空与唯一约束,并可设为自增型;外键约束则通过关联其他表的主键,保证数据一致性。检查约束确保字段值满足特定条件。
62 1
|
3月前
|
数据库 Python
django中数据库外键可以自定义名称吗
django中数据库外键可以自定义名称吗
|
5月前
|
关系型数据库 MySQL 数据库
navicat 查看,设计并导出数据库 ER图
navicat 查看,设计并导出数据库 ER图
931 5
|
4月前
|
存储 算法 Java
数据库范式与设计原则
数据库范式与设计原则
75 0
|
5月前
|
存储 关系型数据库 数据库
关系型数据库设计范式:深入理解与实践
【7月更文挑战第20天】关系型数据库设计范式是数据库设计中的重要指导原则,它通过一系列规范来减少数据冗余、提高数据一致性和优化查询性能。在实际应用中,我们应该根据具体需求和数据特点,灵活选择和应用不同的范式级别,以构建高效、可靠和可扩展的数据库系统。同时,也需要注意范式设计带来的挑战和限制,根据实际情况进行权衡和调整。
|
5月前
|
存储 SQL 关系型数据库
MySQL设计规约问题之在数据库设计中,为什么要适当考虑反范式的表设计
MySQL设计规约问题之在数据库设计中,为什么要适当考虑反范式的表设计
|
1天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
10 3
|
1天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
13 3