数据库设计——约束 学习笔记

已于 2024-07-26 14:24:31 修改
阅读量500 收藏 10
点赞数 15
分类专栏： MYSQL 文章标签： 数据库 学习 笔记 sql
于 2024-07-26 14:24:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lonelyxxyo/article/details/140712922
版权

在数据库设计中，约束是用于确保数据的准确性和完整性的规则。

  1. 非空约束（NOT NULL）

    • 描述：非空约束用于确保字段中的数据不能为null，即必须填写值。
    • 关键字：NOT NULL

  2. 唯一约束（UNIQUE）

    • 描述：唯一约束保证字段中的所有数据都是唯一的，不允许有重复的值。
    • 关键字：UNIQUE

  3. 主键约束（PRIMARY KEY）

    • 描述：主键是表中每一行数据的唯一标识。一个表只能有一个主键，主键字段不能为null，并且必须是唯一的。
    • 关键字：PRIMARY KEY

  4. 默认约束（DEFAULT）

    • 描述：默认约束用于在插入数据时，如果未指定该字段的值，则自动采用默认值。
    • 关键字：DEFAULT

  5. 检查约束（CHECK）

    • 描述：检查约束用于确保字段值满足某个条件。这个约束在MySQL 8.0.16版本之后得到支持。
    • 关键字：CHECK

  6. 外键约束（FOREIGN KEY）

    • 描述：外键约束用于在两个表之间建立关系，确保数据的一致性和完整性。外键通常指向另一个表的主键，用于维护表之间的关系。
    • 关键字：FOREIGN KEY

使用这些约束可以有效地管理数据库中的数据，防止数据错误和不一致性。在设计数据库时，应根据实际需求合理地使用这些约束。

CREATE TABLE tb_user (
  id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
  name varchar(10) NOT NULL UNIQUE COMMENT '姓名',
  age int CHECK (age > 0 AND age <= 120) COMMENT '年龄',
  status char(1) DEFAULT '1' COMMENT '状态',
  gender char(1) COMMENT '性别'
);

上述代码的约束如下

  • id: 字段类型为 int，它是主键，并且会自动增长，用作每条记录的唯一标识。

    • 约束关键字：PRIMARY KEY, AUTO_INCREMENT
    • 约束条件：主键，自动增长

  • name: 字段类型为 varchar(10)，它不能为空，并且必须是唯一的。

    • 约束关键字：NOT NULL, UNIQUE
    • 约束条件：不为空，唯一

  • age: 字段类型为 int，它必须满足大于0并且小于等于120的条件。

    • 约束关键字：CHECK
    • 约束条件：age > 0 AND age <= 120

  • status: 字段类型为 char(1)，如果没有指定值，则默认为 '1'。

    • 约束关键字：DEFAULT
    • 约束条件：默认为 '1'

  • gender: 字段类型为 char(1)，没有指定任何约束条件。

    • 约束关键字：无
    • 约束条件：无

外键约束

添加外键

方法1：在创建表时添加外键约束

在创建表的同时，可以直接在字段定义之后指定外键约束。

CREATE TABLE 表名 (
  字段名1 数据类型1,
  字段名2 数据类型2,
  -- 其他字段定义
  [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

案例

假设我们有一个主表 tb_parent 和一个从表 tb_child。主表 tb_parent 有一个主键字段 parent_id，而从表 tb_child 需要引用这个字段作为外键。

CREATE TABLE tb_child (
  child_id INT PRIMARY KEY,
  parent_id INT,
  -- 其他字段定义
  CONSTRAINT fk_parent_child FOREIGN KEY (parent_id) REFERENCES tb_parent(parent_id)
);

方法2：在已存在的表上添加外键约束

如果表已经存在，可以使用 ALTER TABLE 语句来添加外键约束。

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

案例

假设 tb_child 表已经存在，现在需要添加外键约束 fk_parent_child，将 parent_id 字段作为外键引用 tb_parent 表的 parent_id 字段。

ALTER TABLE tb_child ADD CONSTRAINT fk_parent_child FOREIGN KEY (parent_id) REFERENCES tb_parent(parent_id);

在这两种方法中，[CONSTRAINT] 和 [外键名称] 是可选的，但如果想为外键指定一个名称，这是很有用的，特别是在需要引用或删除外键约束时。外键约束的名称在数据库中必须是唯一的。

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

在执行此操作之前，请确保：

  • 有足够的权限来修改表结构。
  • 删除外键不会破坏数据的完整性或引用关系。
  • 如果外键约束涉及到级联操作，删除它可能会导致相关数据无法正常更新或删除。

可以通过查询数据库的元数据或查看表的结构定义来获取正确的名称。在某些数据库管理系统中，可以使用 SHOW CREATE TABLE 表名; 来查看表的定义，其中包括外键约束的名称。

删除和更新行为

在创建外键约束时，可以指定当父表中的记录被删除或更新时，子表应该采取的行为。

以下是一些删除和更新行为的说明：

删除行为

  • NO ACTION
    说明：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除（与RESTRICT一致）。这是默认行为。

  • RESTRICT
    说明：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除（与NO ACTION一致）。这也是默认行为。

  • CASCADE
    说明：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有，则也删除子表中对应的外键记录。

  • SET NULL
    说明：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。

  • SET DEFAULT
    说明：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则将子表中外键列设置成一个默认的值（InnoDB不支持）。

更新行为

更新行为的选项与删除行为相同，只是它们应用于父表中的记录更新时：

  • NO ACTION / RESTRICT
    说明：当在父表中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。

  • CASCADE
    说明：当在父表中更新对应记录时，首先检查该记录是否有对应外键，如果有，则也更新子表中对应的外键记录。

  • SET NULL
    说明：当在父表中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null。

  • SET DEFAULT
    说明：当在父表中更新对应记录时，首先检查该记录是否有对应外键，如果有则将子表中外键列设置成一个默认的值。

--向已存在的表添加一个外键约束，并指定在更新或删除父表记录时的行为。
ALTER TABLE 表名
ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段)
REFERENCES 主表名 (主表字段名)
ON UPDATE CASCADE
ON DELETE CASCADE;

案例（仅举例说明删除操作的情况，没有提及更新操作）

通过模拟一个数据库环境，创建两个表（parent_table 和 child_table），并在每个表中插入一些数据来说明各种外键删除行为的操作结果。

--创建 parent_table 和 child_table，并设置外键约束
CREATE TABLE parent_table (
  parent_id INT PRIMARY KEY,
  parent_name VARCHAR(50)
);

CREATE TABLE child_table (
  child_id INT PRIMARY KEY,
  parent_id INT,
  child_name VARCHAR(50),
  CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
);
--向 parent_table 和 child_table 中插入一些数据
-- 插入父表数据
INSERT INTO parent_table (parent_id, parent_name) VALUES (1, 'Parent 1');
INSERT INTO parent_table (parent_id, parent_name) VALUES (2, 'Parent 2');
INSERT INTO parent_table (parent_id, parent_name) VALUES (3, 'Parent 3');

-- 插入子表数据
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (101, 1, 'Child 1-1');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (102, 1, 'Child 1-2');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (201, 2, 'Child 2-1');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (301, 3, 'Child 3-1');
NO ACTION / RESTRICT

默认情况下，如果尝试删除 parent_table 中有外键引用的记录，操作将不被允许。

-- 尝试删除父表中的记录，将失败
DELETE FROM parent_table WHERE parent_id = 1;
-- 结果：失败，因为存在外键引用
CASCADE

修改外键约束为 ON DELETE CASCADE

ALTER TABLE child_table DROP FOREIGN KEY fk_child_parent;
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE;

现在，如果删除 parent_table 中的记录，child_table 中对应的记录也将被删除。

-- 删除父表中的记录，子表中对应的记录也将被删除
DELETE FROM parent_table WHERE parent_id = 1;
-- 结果：父表中的记录被删除，子表中 child_id 为 101 和 102 的记录也被删除
SET NULL

修改外键约束为 ON DELETE SET NULL

ALTER TABLE child_table DROP FOREIGN KEY fk_child_parent;
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE SET NULL;

如果删除 parent_table 中的记录，child_table 中对应的外键将被设置为 NULL。

-- 删除父表中的记录，子表中对应的外键将被设置为 NULL
DELETE FROM parent_table WHERE parent_id = 2;
-- 结果：父表中的记录被删除，子表中 child_id 为 201 的记录的 parent_id 被设置为 NULL

SET DEFAULT

（ InnoDB 不支持 ON DELETE SET DEFAULT）如果数据库支持此操作，需要首先确保外键列有一个默认值，然后设置外键约束。

-- 假设支持 SET DEFAULT，则操作如下
ALTER TABLE child_table DROP FOREIGN KEY fk_child_parent;
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE SET DEFAULT;

删除 parent_table 中的记录，child_table 中对应的外键将被设置为默认值。

-- 删除父表中的记录，子表中对应的外键将被设置为默认值
DELETE FROM parent_table WHERE parent_id = 3;
-- 结果：父表中的记录被删除，子表中 child_id 为 301 的记录的 parent_id 被设置为默认值

注：上述示例操作假设在一个支持 SQL 的数据库环境中执行。实际执行时，需要根据自己使用的数据库管理系统的具体语法和功能进行调整。

此处需注意

上述案例的代码语句块之后没有其他语句指定 ON UPDATE 的行为，那么默认情况下，如果父表中的 parent_id 被更新，子表中的对应外键将不会自动更新，可能会违反外键约束，除非该外键值在子表中不存在，或者子表中的对应记录被手动更新。

也就是说，仅指定了当父表中的记录被删除时，子表中对应的外键 parent_id 应该被设置为 NULL。它没有提及当父表中的记录被更新时的行为。默认情况下，如果父表中的 parent_id 被更新，子表中的对应外键不会自动更新，除非数据库系统允许空值或者子表中的对应记录被手动更新。

例如

ALTER TABLE child_table DROP FOREIGN KEY fk_child_parent;
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON UPDATE SET NULL ON DELETE SET NULL;

这个语句块做了以下两件事情：

  1. 删除了 child_table 中名为 fk_child_parent 的外键约束。
  2. 添加了名为 fk_child_parent 的外键约束，并指定了两种行为：
    • 当父表中的记录被更新时，子表中对应的外键 parent_id 应该被设置为 NULL。
    • 当父表中的记录被删除时，子表中对应的外键 parent_id 也应该被设置为 NULL。

这个语句块明确指定了在更新和删除操作时都应该将子表中的外键设置为 NULL，从而确保了外键约束的完整性。

案例（完善）

为了完整性，我将重新模拟数据库环境，创建表，并通过示例操作来展示每种外键删除和更新行为的实际效果。

首先，我们创建一个数据库和一个父表 parent_table，以及一个子表 child_table。

CREATE DATABASE test_db;
USE test_db;

CREATE TABLE parent_table (
  parent_id INT PRIMARY KEY,
  parent_name VARCHAR(50)
);

CREATE TABLE child_table (
  child_id INT PRIMARY KEY,
  parent_id INT,
  child_name VARCHAR(50)
);

插入示例数据

INSERT INTO parent_table (parent_id, parent_name) VALUES (1, 'Parent 1');
INSERT INTO parent_table (parent_id, parent_name) VALUES (2, 'Parent 2');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (101, 1, 'Child 1-1');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (102, 1, 'Child 1-2');
INSERT INTO child_table (child_id, parent_id, child_name) VALUES (201, 2, 'Child 2-1');

示例操作

NO ACTION / RESTRICT

默认情况下，如果尝试删除或更新有外键引用的父表记录，操作将不被允许。

-- 尝试删除父表中的记录，将失败
DELETE FROM parent_table WHERE parent_id = 1;
-- 结果：失败，因为存在外键引用

-- 尝试更新父表中的记录，将失败
UPDATE parent_table SET parent_id = 3 WHERE parent_id = 1;
-- 结果：失败，因为存在外键引用
CASCADE

修改外键约束为 ON DELETE CASCADE 和 ON UPDATE CASCADE。

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- 删除父表中的记录，子表中对应的记录也将被删除
DELETE FROM parent_table WHERE parent_id = 1;
-- 结果：父表中的记录被删除，子表中 child_id 为 101 和 102 的记录也被删除

-- 更新父表中的记录，子表中对应的外键记录也会更新
UPDATE parent_table SET parent_id = 3 WHERE parent_id = 2;
-- 结果：父表中的记录更新，子表中 child_id 为 201 的记录的 parent_id 也更新为 3
SET NULL

修改外键约束为 ON DELETE SET NULL 和 ON UPDATE SET NULL。

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON DELETE SET NULL
ON UPDATE SET NULL;

-- 删除父表中的记录，子表中对应的外键将被设置为 NULL
DELETE FROM parent_table WHERE parent_id = 2;
-- 结果：父表中的记录被删除，子表中 child_id 为 201 的记录的 parent_id 被设置为 NULL

-- 更新父表中的记录，子表中对应的外键将被设置为 NULL
UPDATE parent_table SET parent_id = 4 WHERE parent_id = 3;
-- 结果：父表中的记录更新，子表中 child_id 为 201 的记录的 parent_id 被设置为 NULL
Cici_ovo
关注
  • 15
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库系统概论——个人学习笔记
qq_53092934的博客
03-20 645
系列文章目录 提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加 例如：第一章 Python 机器学习入门之pandas的使用 提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录系列文章目录前言一、pandas是什么？二、使用步骤1.引入库2.读入数据总结 前言 提示：这里可以添加本文要记录的大概内容： 例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。 提示：以下是本篇文章正文内容，下面案例
《数据库系统概念》学习笔记——关系数据库设计
weixin_45243288的博客
12-12 2007
数据库系统概念——关系数据库设计关系数据库设计好的关系设计的特点设计选择：更大的模式设计选择：更小的模式原子域和第一范式使用函数依赖进行分解码和函数依赖Boyce-Codd 范式BCNF 和保持依赖第三范式更高的范式函数依赖理论函数依赖集的闭包属性集的闭包正则覆盖无损分解保持依赖分解算法BCNF 分解3NF 分解3NF 算法的正确性BCNF 和 3NF 的比较使用多值依赖的分解多值依赖第四范式4NF 分解更多的范式数据库设计过程E-R 模型和规范化属性和联系的命名为了性能去规范化其他设计问题时态数据建模 关
数据库学习笔记——unique约束
热门推荐
weixin_40055077的博客
07-24 2万+
unique约束注意点： 1、定义了unique约束的字段中不能包含重复值 2、可以为一个或多个字段定义unique约束 3、unique约束中可以包含null空值（unique只要求每列的值不一样，当值是null时也算是不一样） unique约束的添加：（unique约束可以通过两种方法添加，一种是在建表时添加，另一种是在建表完成后添加） 一、建表时添加： 只为单列添加unique约...
【教程】MySQL数据库学习笔记（五）——约束（持续更新）
EliasChang的博客
04-21 1564
MySQL数据库学习第五部分
数据库学习笔记（三）—— 数据库设计
weixin_46056458的博客
02-26 535
目录一、数据库设计二、E-R图绘制三、数据库规范——三大范式 一、数据库设计 为什么要设计数据库 二、E-R图绘制 三、数据库规范——三大范式
《数据库系统概论》学习笔记——王珊 萨师煊
qq_39991776的博客
12-04 2239
数据库学习笔记
数据库学习笔记第十二章：数据库设计方法
m0_73721855的博客
06-18 906
按照结构化系统设计的方法,考虑数据库及其应用系统开发全过程,研究数据库设计的基本步骤。数据库设计分为六个阶段,分别是:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。需求分析和概念结构设计独立于任何数据库管理系统逻辑结构设计和物理结构设计则与选用的数据库管理系统密切相关数据库设计的准备工作：必须选定参加的设计人员包括：1.系统分析人员、数据库设计人员自始至终参与数据库设计2.数据库管理员和用户代表主要参加需求分析和数据库的运行和维护。
《数据库系统概念》学习笔记——数据库设计和 E-R模型
weixin_45243288的博客
12-07 1745
数据库系统概念——数据库设计和 E-R模型数据库设计和 E-R模型设计过程概览设计阶段设计选择实体 - 联系 模型实体集联系集属性约束映射基数参与约束码从实体集中删除冗余属性实体 - 联系图基本结构映射基数复杂的属性角色非二元的联系集弱实体集大学的E-R图转换为关系模式具有简单属性的强实体集的表示具有复杂属性的强实体集的表示弱实体集的表示联系集的表示实体-联系设计问题用实体集还是用属性用实体集还是用联系集二元还是n元联系集联系属性的布局扩展的E-R特性特化概化属性继承概化上的约束聚集转换为关系模式数据建模的
东北大学——数据库概论——学习笔记 第01章 引言
Fland22的博客
04-27 801
第01章 引言 0. DBMS DB DBS 数据库管理系统（DataBase-Management System，DBMS）由一个互相关联的数据集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库，DBMS的主要目标是要提供一种可以方便、高效地存取数据库信息的途径。用户通过DBMS访问数据库（DB）中的数据。 数据库系统（DataBase System）是一个统称，包括数据库、数据库管理系统、数据库管理人员等的统称，是最大的范畴。 DBS包含DB和DBMS 1. 数据库系统的目标 文件
MySQL学习笔记——函数和约束
君莫笑lucky的博客
06-21 882
学习黑马MySQL课程记录笔记，用于复习。
数据库——甲骨文学习笔记.doc
06-05
本文档是一份关于Oracle学习的笔记，旨在帮助读者掌握Oracle数据库的基本操作和新特性。 首先，我们从创建表开始，这是数据库管理的基础。创建表涉及到定义表的结构，包括列名、数据类型、约束条件等。例如，创建表...
JPA学习笔记（二）——JPA 注解
12-14
`@Table` 注解用来指定实体对应的数据库表名，可以包含其他属性如`uniqueConstraints`来定义唯一约束。 3. **@Id** `@Id` 注解用于标记一个字段作为主键，每个实体至少有一个主键字段。如果需要自动生成主键，...
Oracle学习笔记——day04
09-02
在Oracle学习笔记的第四天，我们探讨了数据库管理和操作的一些核心概念，主要集中在DDL（数据定义语言）、DML（数据操作语言）、DCL（数据控制语言）以及事务管理。以下是对这些内容的详细阐述： 首先，我们看到了...
Oracle学习笔记——day03
09-02
在Oracle数据库的学习中，第三天的内容...在学习Oracle的过程中，理解并掌握这些基本数据类型和约束是非常关键的，它们构成了数据库设计的基础。通过实践操作，我们可以更好地理解这些概念，并在实际应用中灵活运用。
Java毕业设计——基于ssm的兼职论坛设计与实现（源码+数据库）.zip
09-29
- 数据库设计是系统的核心，合理的数据模型可以优化查询性能和数据一致性，例如，可能使用外键约束来维护数据的一致性，索引来提升查询速度。 4. **业务逻辑**： - 用户模块：用户注册、登录、个人信息管理等功能...
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 672
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
04-用户画像+sqoop使用
weixin_46567476的博客
07-21 569
sqoop的作用是实现数据的导入和导出，主要是对数据库和数据仓库之间的操作。qoop脚本就是将sqoop指令写入shell文件 后缀是 .sh。只要是支持jdbc连接的数据库都可以使用sqoop操作。
redis常用指令
最新发布
m0_62289159的博客
07-22 785
redis常用指令
Django cursor()增删改查和shell环境执行脚本
人生苦短，何妨一试
07-21 482
在Django中，cursor()方法是DatabaseWrapper对象（由django.db.connectio提供）的一个方法，用于创建一个游标对象。这个游标对象可以用来执行SQL命令，从而实现对数据库的增删改查操作。使用cursor.execute()方法执行SQL查询语句，可以获取数据库中的数据。查询your_table表中的所有记录。执行查询后，你可以使用cursor.fetchall()或cursor.fetchone()等方法来获取查询结果。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值