MySQL（五）——表设计(约束、范式、表关系)

本文主要是介绍MySQL（五）——表设计(约束、范式、表关系)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

表设计

在MySQL中，表设计是一个关键的环节，它涉及到约束、范式和表关系等多个方面。

约束

在MySQL中，约束（Constraints）是数据库设计的重要组成部分，用于限制表中数据的类型和格式，以确保数据的准确性和可靠性。约束可以在创建表时定义，也可以在表创建后通过ALTER TABLE语句进行修改。

不同的约束类型本身并不直接相互冲突，一个字段可以同时有多个约束，这要视情况而定。

下面我们就介绍一下最常见的约束类型：

非空约束（NOT NULL）

定义：非空约束用于确保表中的某个字段不接受NULL值。

特点：非空约束是列级约束，只能应用于列的定义上。

创建示例：

例如，创建学生表时不希望联系方式是NULL值：

# 创建示例，仅举例，非最终版
CREATE TABLE IF NOT EXISTS stu1(id BIGINT,name VARCHAR(30),contact VARCHAR(20) NOT NULL
);# 以下SQL均会报错
INSERT INTO stu1 VALUES (1, 'zhang', NULL);
INSERT INTO stu1 (id,name) VALUES (1, 'zhang');# 展示表结构
DESC stu1;

表结构：

• 由NOT NULL约束的字段的 NULL 列显示为 NO，表示不能为空

---

唯一约束（UNIQUE）

定义：唯一约束用于确保表中的一列或多列的值是唯一的， 即表中任何两行在这些列上的组合值都不相同。

特点：唯一约束允许NULL值存在，但每个NULL值都被视为不同的值。

创建示例：

例如，创建学生表时限制学号必须是唯一的，不能重复：

# 创建示例，仅举例，非最终版
CREATE TABLE IF NOT EXISTS stu2(id BIGINT,name VARCHAR(30),sn VARCHAR(20) UNIQUE
);# 以下SQL会报错，123456的重复违反了唯一键约束
INSERT INTO stu2 VALUES (1, 'zhang', 123456);
INSERT INTO stu2 VALUES (2, 'li', 123456);# 以下SQL不会报错，因为 UNIQUE 约束将两个NULL值视为“不相同”的实例
INSERT INTO stu2 VALUES (3, 'wang', NULL);
INSERT INTO stu2 VALUES (4, 'zhao', NULL);# 展示表结构
DESC stu2;

表结构：

• 由UNIQUE约束的字段的 KEY 列显示为 UNI，表示唯一键约束

---

主键约束（PRIMARY KEY）

定义：主键约束用于唯一标识表中的每一行记录。主键列的值不能重复，也不能为NULL。

特点：主键约束相当于唯一约束(UNIQUE) + 非空约束(NOT NULL)的组合。一个表只能有一个主键，但主键可以包含多个字段（称为复合主键）。

---

【单列主键】

我们习惯每张表都创建一个id字段，且一般都会指定这个字段为主键字段，唯一标识表中的每一行记录。

创建示例：

例如，创建学生表时将id字段设置为主键字段，该字段既不能为NULL，值也不能重复：

# 创建示例，仅举例，非最终版
CREATE TABLE IF NOT EXISTS stu3(id BIGINT PRIMARY KEY,name VARCHAR(30),gender TINYINT
);# 展示表结构
DESC stu3;

表结构：

• 由PRIMARY KEY 约束的字段的 NULL 列显示 为 NO，表示不能为空；KEY 列显示 PRI ，表示主键约束

---

【复合主键】

如果单个列无法唯一地标识表中每一行，就要用到复合主键。 复合主键不在类型后面声明，而是在所有字段之后声明，后接一个小括号，里面是参与组成复合主键的列，之间用逗号分隔。

复合主键的特点：复合主键一定包含多个列，只有当这些列的值都相等时，才会判定为重复记录；复合主键列不能为NULL

比如，学生选课记录表，包含学生id、所选课id以及选课时间。此时学生id无法标识每一行，因为一门课可以多个学生选，一个学生也可以选多门课，此时我们使用复合主键：

# 创建示例，仅举例，非最终版
CREATE TABLE IF NOT EXISTS stu4(student_id BIGINT,course_id BIGINT,ch_time DATE,PRIMARY KEY (student_id, course_id)
);# 报错！复合主键列不能为NULL
INSERT INTO stu4 VALUES (NULL,1,'2010-9-23');# 报错！复合主键的所有列的值都相等，判定为重复记录
INSERT INTO stu4 VALUES (1,21,'2010-9-23'),(1,21,'2010-9-24');#不报错。因为不是所有的复合主键列都相等，不会被判定为重复记录
INSERT INTO stu4 VALUES (1,21,'2010-9-23'),(2,21,'2010-9-24');# 展示表结构
DESC stu4;

表结构：

• 复合主键的每个列对应的字段的 NULL 列均显示 为 NO，表示不能为空；KEY 列均显示 PRI ，表示主键约束

---

【非空约束+唯一约束 和 主键约束 的区别】

主键约束天然拥有非空和唯一的特性，但这不代表NOT NULL + UNIQUE等于主键约束。

特性	NOT NULL + UNIQUE	PRIMARY KEY
非空性	√	√（自动包含）
唯一性	√	√（自动包含）
每张表可设置数量	多个	一个（单列主键或复合主键）
自动索引	×	√
额外功能	无特定额外功能	作为表的唯一标识，常用于关系表的外键引用

• 两者可以同时存在
• NOT NULL + UNIQUE 约束用于确保列中的值既非空又唯一，适用于需要这种双重保证的字段。
• PRIMARY KEY 约束则是一个更高级别的约束，它自动包含了 NOT NULL 和 UNIQUE 的特性，并额外提供了作为表唯一标识和自动索引的功能。

---

【现象的解释】

一张表创建多个NOT NULL + UNIQUE约束，并查看表结构。

CREATE TABLE IF NOT EXISTS demo3(id BIGINT NOT NULL UNIQUE,name VARCHAR(30) NOT NULL UNIQUE,gender TINYINT NOT NULL UNIQUE
);
DESC demo3;

现象：这张表实际没有主键约束，但其中一个由NOT NULL + UNIQUE约束的字段的 KEY 显示 PRI

如果表中没有显式定义主键（PRIMARY KEY），但有多个字段被设置为UNIQUE + NOT NULL并且没有其它字段被明确指定为主键，MySQL的行为可能会因版本和具体配置而异，但在某些情况下，它可能会以一种“启发式”的方式选择一个UNIQUE + NOT NULL字段（或字段组合，如果表定义中有复合唯一索引）来作为表的“隐式主键”的显示方式，尽管在严格意义上，这个字段并没有被定义为主键，这通常是一个错误或误导！

我们只要清楚，这种情况下，该表不存在主键，可以使用SHOW CREATE TABLE tbl_name;语句查看建表语句，根据结果判断表中是否有主键。

Create Table列的所有内容如下：

/*
CREATE TABLE `demo3` (`id` bigint NOT NULL,`name` varchar(30) NOT NULL,`gender` tinyint NOT NULL,UNIQUE KEY `id` (`id`),UNIQUE KEY `name` (`name`),UNIQUE KEY `gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*

• 发现：三个字段的约束类型都是NOT NULL + UNIQUE，不存在主键约束。

---

外键约束（FOREIGN KEY）

定义：外键约束用于在两个表之间建立关联，以保证数据的一致性和完整性。外键是子表(从表)中的一个字段，它指向父表(主表)的主键字段或者唯一键字段。

特点：外键约束可以确保子表中的外键值必须在父表的主键列中存在，或者在子表中该外键列的值可以为NULL（如果允许的话）

建表时语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (  field1 datatype [constraints] [COMMENT 'string'],  field2 datatype [constraints] [COMMENT 'string'],  field3 datatype [constraints] [COMMENT 'string'],  ...  -- 假设这里是外键字段所在的列  foreign_key_field datatype,  ...  -- 字段定义结束后，添加外键约束  [CONSTRAINT 外键名称] FOREIGN KEY (foreign_key_field) REFERENCES 主表(主表列名)  ON UPDATE 规则  ON DELETE 规则  
);

• 约束名称：CONSTRAINT 关键字是可选的，但指定一个名称可以帮助你在需要时更容易地引用或删除该约束。
• 行为规则：这是有外键联系的表的删除/更新的行为规则，下面有介绍。

建表后添加外键和删除外键语法：

# 添加外键
ALTER TABLE tbl_name ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);# 删除外键
ALTER TABLE tbl_name DROP FOREIGN KEY 外键名称;

---

创建示例：

如下表：

员工表中没有直接存储部门名称，而是存储了部门id，与部门表的 id 建立了联系，这时候，如果部门表的 id 是主键字段或唯一键字段（建表时通常会这么做），那么就可以将员工表的 dept_id 字段设置成外键字段，与部门表的 id 字段建立联系。

建表语句如下：

CREATE TABLE IF NOT EXISTS dept_table(id BIGINT PRIMARY KEY,name VARCHAR(20) NOT NULL
);CREATE TABLE IF NOT EXISTS emp_table(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL,dept_id BIGINT,FOREIGN KEY (dept_id) REFERENCES dept_table (id)
);# 查看从表结构
DESC emp_table;

• 从表的外键字段的 KEY 列显示 MUL

---

【有外键联系的表的行为】

• 插入行为：当向从表中添加新数据时，从表中外键字段的值必须在主表对应主键（或唯一）列中存在，否则会因校验而报错！除非从表的外键字段插入的是NULL值（前提保证该字段允许为NULL）
• 更新/删除行为：默认行为规则：当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新/删除（避免了主表删除某些记录后，从表外键列中可能存在主表主键列不存在的值，即没有连接关系了）

---

以下为删除/更新行为的规则：

行为规则	说明
NO ACTION	当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新/删除（默认规则，与RESTRICT一致）
RESTRICT	当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新/删除（默认规则，与RESTRICT一致）
CASCADE	当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有，则也更新/删除外键在子表中的记录
SET NULL	当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为NULL(前提是该外键字段允许为NULL)
SET DEFAULT	当在主表中更新/删除对应记录时，首先检查该记录是否有对应外键，如果有，则将子表的外键列设置成一个默认的值（默认存储引擎Innodb不支持）

---

我们可以随时手动修改更新/删除行为的规则！修改规则的逻辑是先删除原有外键约束，重新生成新的外键约束并同时指定新的规则。

删除外键的语法上面已经给出，我们需要知道外键约束名称，查询名称的语法：

SELECT CONSTRAINT_NAME   
FROM information_schema.TABLE_CONSTRAINTS   
WHERE TABLE_NAME = 'tbl_name' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
# 查询时只需要将tbl_name换成自己的表名即可

查询到后删除原有的外键约束，接着生成新的外键约束（生成新的外键约束一定要遵循外键约束的规则，比如，不能出现 要设置外键的字段列中含有主表中外键关联列中不存在的数据 的情况，这样会导致创建外键约束失败）；

生成新的外键约束并指定新规则的语法：

ALTER TABLE tbl_name 
ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) 
REFERENCES 主表 (主表列名) 
ON UPDATE 规则 
ON DELETE 规则;

• ALTER TABLE tbl_name：指定要修改的表名。
• ADD CONSTRAINT 外键名称：添加一个新的约束，并为其指定一个名称（即外键名称）。这个名称在数据库中必须是唯一的，用于标识这个外键约束。
• FOREIGN KEY (外键字段名)：定义当前表中哪个字段是外键。外键字段用于存储对另一个表（即主表）中记录的引用。
• REFERENCES 主表 (主表列名)：指定这个外键字段引用的主表及其列名。外键字段中的值必须是主表列中存在的值。
• ON UPDATE 规则：定义当主表中的对应列的值被更新时，外键字段应该如何响应。常见的规则包括 CASCADE（级联更新）、SET NULL（将外键字段设置为 NULL）、NO ACTION（如果尝试更新会导致违反外键约束，则拒绝更新）等。
• ON DELETE 规则：定义当主表中的记录被删除时，外键字段应该如何响应。规则与 ON UPDATE 类似，但应用于删除操作。

---

例如：

ALTER TABLE emp_table 
ADD CONSTRAINT dept_id_id FOREIGN KEY (dept_id) 
REFERENCES dept_table (id) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

将更新/删除规则均改为CASCADE，此时当在主表 dept_table 中更新/删除对应记录时，对应外键也会更新/删除外键在子表 emp_table 中的记录。

---

【思考】 为什么外键字段必须和另一张表的主键字段或唯一键字段建立联系呢？

答案就是唯一性，如果外键字段连接的字段的值可以重复，那么通过外键字段的值寻找时，可能会找到两甚至多个相同的值，此时外键字段的这个值就无法判断和主表的哪条记录有联系了。

【思考】 外键字段的类型和主键字段类型必须一致吗？

为了保持数据的一致性、提高性能以及易于维护，通常建议外键和主键的类型应该是一致的。如果由于某些特殊原因需要它们类型不一致，数据库可能会在内部尝试进行类型转换以进行匹配。我们应该仔细考虑这种设计的潜在影响，并在必要时采取额外的措施来确保数据的正确性和完整性。

【思考】 外键约束的优点？

通过维护数据之间的引用完整性和一致性，确保了数据库的可靠性和准确性。

---

默认值约束（DEFAULT）

定义：默认值约束用于在插入记录时，为表中某些字段指定默认值。

特点：如果在插入记录时没有为某个字段指定值，则MySQL会自动为该字段赋予默认值。

创建示例：

例如，对于选择不公开名字的同学，将他们的名字默认为’无名氏’：

CREATE TABLE IF NOT EXISTS stu5(id BIGINT PRIMARY KEY,name VARCHAR(30) DEFAULT '无名氏',gender TINYINT
);# 以下SQL语句不会报错
INSERT INTO stu5 VALUES (1, '张三', 1),(2, '李四', 0);
INSERT INTO stu5 VALUES (3, NULL, 1);
INSERT INTO stu5 (id, gender) VALUES (4, 0);             # 查询表数据
SELECT * FROM stu5;# 查看表结构
DESC stu5;

表中数据：

• 对于由默认值约束的列，只有不手动插入任何值时才会使用默认值；手动插入NULL值也不会使用默认值，而是使用NULL值，可以理解为用户指定的优先级高于默认约束。

表结构：

• 由DEFAULT约束的字段的 Default 列会显示 设定的默认值。

---

检查约束（CHECK）

定义：检查约束用于限制列中值的范围，确保列中的值满足特定的条件。

特点：MySQL在8.0.16及以后的版本中支持CHECK约束。

检查约束在所有字段+类型之后指定，后接小括号，里面指定要检查的列及条件。

创建示例：

例如，创建一份人口信息表，含有年龄字段，要求年龄字段的数据在0~120之间：

CREATE TABLE IF NOT EXISTS personinfo(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL DEFAULT '无名氏',age SMALLINT,CHECK (age BETWEEN 0 AND 120)
);# 以下SQL语句会报错，违法了CHECK 约束
INSERT INTO personinfo VALUES (1, '张三', -1);
INSERT INTO personinfo VALUES (1, '张三', 121);# 查看表结构
DESC personinfo;

表结构：

• 由CHECK约束的字段在表结构中没有具体体现

---

零填充约束（ZEROFILL）

定义：当字段启用零填充约束后，如果插入的数值长度小于定义的显示宽度，MySQL会在该数值的前面填充零，以达到指定的宽度。

特点：主要适用于整型字段（如INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT等），当使用零填充约束时，MySQL默认会自动给该字段添加UNSIGNED属性，这意味着该字段只能存储非负值。

指定显示宽度的方法：在类型的后面加上小括号，小括号里指定显示宽度。

显示宽度主要影响的是显示格式，而非数据本身的大小或存储方式。即使设置了显示宽度，数据的存储大小仍然由数据类型决定（如INT类型总是占用4个字节）。

创建示例：

例如，创建一个学生表，要求学号由5位组成，左边填充0：

CREATE TABLE IF NOT EXISTS stu6(id BIGINT(5) ZEROFILL PRIMARY KEY,name VARCHAR(30) NOT NULL,gender TINYINT
);INSERT INTO stu6 VALUES (1, 'zhang', 1),(12, 'li', 1),(123, 'wang', 0); # 查看表数据
SELECT * FROM stu6;# 查看表结构
DESC stu6;

表数据：

表结构：

• 由ZEROFILL约束的整型字段的 TYPE 列显示为类型(显示宽度) unsigned zerofill

---

自动递增约束（AUTO_INCREMENT）

定义：自动递增约束用于为表中的主键字段自动生成唯一的数值。

特点：每次向表中插入新记录时，MySQL会自动为具有AUTO_INCREMENT约束的字段增加一个唯一的值。

• 默认情况下，AUTO_INCREMENT的初始值是 1，每新增一条记录，字段值自动加 1
• 一个表中只能有一个AUTO_INCREMENT字段
• AUTO_INCREMENT必须配合主键一起使用，并且只适用于整数类型

---

行为规则（对于AUTO_INCREMENT字段）：

• 插入行为：

  1. 显式地插入NULL值 和 插入时没有指定任何值：

     MySQL 会为该字段分配下一个可用的 AUTO_INCREMENT 值，该值为默认为1，之后会追踪当前表中 AUTO_INCREMENT 字段的最大值。当插入新行时，MySQL 会计算下一个可用的 AUTO_INCREMENT 值，这个值是当前最大值加 1。

  2. 插入时指定了比当前AUTO_INCREMENT值小的值(该值一定是唯一的表中不存在地)：

     允许该插入，但不会改变 AUTO_INCREMENT 的值。

  3. 插入时指定了比当前所有AUTO_INCREMENT字段值都大的值：

     允许该插入，并更新 AUTO_INCREMENT 的值为你指定的值加1（为大于或等于你指定值的第一个未被使用的值加1）。

• 删除行为：

  当你从表中删除记录时，AUTO_INCREMENT的值不会自动减少或重置，AUTO_INCREMENT的值仍然会递增，以保持唯一性。例如，如果你的表最后插入的行的AUTO_INCREMENT值为10，然后删除了这条记录，下一条插入的记录的AUTO_INCREMENT值将会是11，而不是10或更小的值。

创建示例：

CREATE TABLE IF NOT EXISTS stu8(id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30) NOT NULL,gender TINYINT
);INSERT INTO stu8 VALUES (NULL, 'zhang', 1),(NULL, 'li', 0);  -- id分配1和2
DELETE FROM stu8 WHERE id = 2;                               -- AUTO_INCREMENT不会减少或重置
INSERT INTO stu8 VALUES (NULL, 'wang', 1);                   -- id分配3
INSERT INTO stu8 VALUES (10, 'liu', 1);                      -- id接受10,并更新AUTO_INCREMENT的值
INSERT INTO stu8 VALUES (5, 'sun', 1);                    -- id接受5，该值不是自动分配的，5不会导致AUTO_INCREMENT值更新# 查看表数据
SELECT * FROM stu8;# 查看表结构
DESC stu8;

表数据：

表结构：

• AUTO_INCREMENT字段会在 Extra 列显示

---

修改自动增长初始值：

创建表时指定，例如：

CREATE TABLE IF NOT EXISTS stu8(id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30) NOT NULL,gender TINYINT
)AUTO_INCREMENT = 100;

• 上语句设置AUTO_INCREMENT的值为100

---

创建表后修改：

ALTER TABLE tbl_name AUTO_INCREMENT [=] val;

• val就是要设置的值，如果你尝试设置的AUTO_INCREMENT值小于或等于表中当前任何行的AUTO_INCREMENT字段的最大值，MySQL将不会降低这个值。MySQL会保留一个比表中任何现有AUTO_INCREMENT值都大的AUTO_INCREMENT值，以确保新插入的行获得唯一的标识符。

---

查看当前的AUTO_INCREMENT值：

# 方法一：表中寻找
SHOW TABLE STATUS LIKE 'your_table_name'; -- 替换为你的表名# 方法二：直接返回
SELECT AUTO_INCREMENT  
FROM information_schema.TABLES  
WHERE TABLE_SCHEMA = 'your_database_name' -- 替换为你的数据库名  
AND TABLE_NAME = 'your_table_name'; -- 替换为你的表名

---

范式

范式是设计关系型数据库时遵循的一系列规范要求，旨在减少数据冗余、提高数据存储效率和查询性能。这些规范要求被称为不同的范式，共有六种，从低到高依次为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。

最常见的是三大范式（前三大），其他范式应用于更高数据库要求的场景，但随着范式级别的提高，数据库的设计会变得更加复杂，同时可能带来性能上的开销。

---

第一范式（1NF）

定义：

确保表中的每一列都是不可分割的原子数据项，即每个字段都应该是不可再拆分的最小单位。

示例：

假设一个“学生信息”表中有一个字段为“学校”，如果它同时存储了学校名称和学校地址，那么该字段就是可分割的，不符合1NF。正确的做法是将这个字段拆分为“学校名称”和“学校地址”两个字段。

一张表的所有属性均能被MySQL的一个数据类型表示，那么这张表就天然满足第一范式。 如，学校不能被任何一个MySQL数据类型表示，因为它需要包含学校名称，学校地址等多个数据。

不满足第一范式（1NF）的表通常不被视为关系型数据库中的有效表。

---

第二范式（2NF）

定义：

在满足第一范式的基础上，表中的每一行或每一个实例都可以被唯一地区分，且表中的非主属性完全依赖于主键。即不存在部分依赖于主键的非主属性。

（在1NF的基础上，消除部分依赖。即，非主属性完全依赖于主键）

示例：

例如，一张表示学生选课信息的表，包含学生ID、学生姓名、学生班级、课程ID、课程名称、课程类别，采用学生ID和课程ID组成复合主键唯一表示学生和课程的关系，此时，学生姓名，学生班级只依赖于学生ID，不依赖于课程ID，构成对主键的部分依赖，因此，不满足2NF。同样地，课程名称、课程类别只依赖于课程ID，不依赖于学生ID，构成对主键的部分依赖，不满足2NF。

一张表不存在复合主键，那么这张表天然满足第二范式。除非，这张表需要用到复合主键约束却没有使用。

---

第三范式（3NF）

定义：

在满足第二范式的基础上，表中的非主属性不依赖于其他非主属性，即不存在非主属性对主键的传递依赖。

（在2NF的基础上，消除传递依赖。即，非主属性不依赖于其他非主属性）

示例：

有一张学生信息表，包括学号、姓名、年龄、所在学院、学院电话字段，学号作为主键，此时存在学号 -> 所在学院 -> 学院电话 这样的传递关系，非主属性学院电话依赖于非主属性所在学院，因此不满足3NF。

综上所述，一个表要天然满足第三范式，它必须首先满足第一范式和第二范式，并且不存在任何传递依赖。

---

巴斯-科德范式（BCNF）

定义：

巴斯-科德范式是第三范式的加强版，它要求所有属性都完全依赖于候选键（不仅仅是主键），且不存在任何属性对候选键的部分依赖或传递依赖。

候选键： 能够唯一标识表中每一行记录的属性或属性集。换句话说，表中的任何两行都不能在候选键上拥有完全相同的值。

需要注意的是，一个表可以有多个候选键。但是，通常我们会选择其中一个作为主键（Primary Key），用于在数据库表中唯一标识每一行记录。主键是候选键的一个特例，它是被选定用于此目的的候选键。

示例：

一个订单表，包含字段“订单号”、“顾客ID”、“顾客姓名”、“订单金额”。如果“顾客姓名”完全依赖于“顾客ID”，而“顾客ID”是“订单号”和“顾客ID”这一复合候选键的一部分，那么表满足第三范式，但不满足BCNF，因为“顾客姓名”部分依赖于复合候选键的一部分（即“顾客ID”）。

---

第四范式（4NF）

定义:

第四范式要求关系模式中的多值依赖必须是平凡的，即不允许有非平凡且非函数依赖的多值依赖。这主要处理的是表中存在多个独立的多值属性的情况。

（在BCNF的基础上，消除多值依赖。即，表中不应该存在多值依赖）

示例：

一个教师可能教授多门课程，每门课程又可能有多个学生选修。这种情况下，可能需要设计专门的表来存储这种多对多的关系，以满足第四范式的要求。

---

第五范式 （5NF）

定义：

第五范式是在第四范式的基础上进一步规范化，它要求表必须可以分解为更小的表，除非这些表在逻辑上拥有与原始表相同的主键。这主要处理的是相互依赖的多值情况。

（在4NF的基础上，消除连接依赖。即，表中不应该存在连接依赖）

示例：

第五范式的例子同样较为复杂，且在实际应用中相对较少见。

一个学生选课表，包含学号、课程号、成绩等字段。如果一个学生可以选修多个课程，那么课程号就对学号产生了连接依赖。为了满足5NF，需要将学生选课表拆分为学生表、课程表和选课表。

---

表关系

表关系是指数据库中不同表之间的关联或联系，这些关系可以用来描述实体间的各种联系，如一对一、一对多和多对多关系。根据不同的关系，都有一套相对固定的建表方式，能够较好的满足范式要求，做到数据完整性，提高性能，并易于维护。

实际设计表的时候，Java中的一个类可以对应到数据库中的一个实体（可以简单认为是一张表），Java类中的每一个字段可以对应到一张表的属性。因此表关系可以引申到类关系。

所以在实际设计表时，我们首先要从需求中提取类，然后再进一步分析不同类的关系，进而确定合适的建表方式。

---

一对一

定义：在一对一关系中，一张表中的某一项数据只能与另一张表中的一项数据相对应，并且反之亦然。

应用场景：例如，某些场景下，一个用户只能有一个账号，一个账号对应一个用户。

建表方式：

• 将两个实体的属性放在一张表中。

• 创建两张表，分别存放两个实体的信息，利用外键关联两张表。

  例如，用户信息表中设置一个外键字段关联到账号信息表中的主键字段，建表SQL：

  CREATE TABLE IF NOT EXISTS account(id BIGINT PRIMARY KEY,username VARCHAR(20) NOT NULL,password VARCHAR(20) DEFAULT '123456'
  );CREATE TABLE IF NOT EXISTS users(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL,age INT,account_id BIGINT,CHECK (age BETWEEN 0 AND 120),FOREIGN KEY (account_id) REFERENCES account (id)
  );
  

---

一对多

定义：在一对多关系中，一张表（父表）中的某项数据可以与另一张表（子表）中的多项数据相对应，但子表中的数据只能对应父表中的一项数据。

应用场景：例如，一个班级可以有多个学生，但每个学生只属于一个班级。

建表方式：

• 创建两张表，建立主外键关系，将"一"设计为主表，将"多"设计为从表（建立外键字段关联主表的主键）。

  例如，创建学生表和班级表，学生作为"多"，设计一个外键字段，如class_id，与班级表的主键字段建立联系

  CREATE TABLE IF NOT EXISTS class(id BIGINT PRIMARY KEY,name VARCHAR(20) NOT NULL
  );CREATE TABLE IF NOT EXISTS students(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL,gender TINYINT,class_id BIGINT,FOREIGN KEY (class_id) REFERENCES class (id)
  );
  

---

多对多

定义：在多对多关系中，一张表中的某项数据可以与另一张表中的多项数据相对应，反之亦然。

应用场景：例如，一个学生可以选修多门课程，而一门课程也可以被多个学生选修。

建表方式：

• 分别建立两个实体表，但这种方式不存在表的联系，不推荐！

• 建立两个实体表后，额外建立关系表，表中设置两个外键关联两个实体表的主键，在关系表中为实体间建立关联关系。

  CREATE TABLE IF NOT EXISTS courses(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL
  );CREATE TABLE IF NOT EXISTS students(id BIGINT PRIMARY KEY,name VARCHAR(30) NOT NULL,gender TINYINT
  );CREATE TABLE IF NOT EXISTS courses_students(id BIGINT PRIMARY KEY,course_id BIGINT,student_id BIGINT,FOREIGN KEY (course_id) REFERENCES courses (id),FOREIGN KEY (student_id) REFERENCES students(id)
  );
  

---

完

这篇关于MySQL（五）——表设计(约束、范式、表关系)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---