Mysql--基础篇--约束（主键，外键，唯一，检查，枚举及复合约束等）

embedded/2025/1/15 6:44:14/

在MySQL中，约束（Constraints）是用于确保数据库中数据完整性和一致性的规则。通过定义约束，可以防止用户插入、更新或删除不符合特定条件的数据，从而保证数据的准确性和可靠性。MySQL支持多种类型的约束，每种约束都有其特定的作用和使用场景。

1、主键约束（Primary Key Constraint）

作用：

  • 唯一性：确保表中的每一行都有一个唯一的标识符。
  • 非空性：主键列不允许包含NULL值。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键，自动递增name VARCHAR(50) NOT NULL,age INT
);

解释：

  • id列是主键，确保每个员工都有一个唯一的标识符，并且该列不允许为NULL。
  • AUTO_INCREMENT：自动为每一行生成唯一的ID，通常用于主键列。

或者在创建表后添加主键：

ALTER TABLE table_name
ADD PRIMARY KEY (column1);

特点：

  • 每个表只能有一个主键。
  • 主键可以由单个列或多列组成（称为复合主键）。
  • 主键列上的值必须是唯一的且不能为空。

2、外键约束（Foreign Key Constraint）

作用：

  • 引用完整性：确保两个表之间的关系。外键列的值必须存在于另一个表的主键列中，或者为NULL。
  • 级联操作：可以通过外键定义级联操作（如删除或更新时自动影响相关记录）。

示例：

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(id)
);

解释：

  • orders表中的customer_id列是外键，引用customers表中的id列。
  • 这意味着orders表中的customer_id必须是customers表中存在的id，或者为NULL。

或者在创建表后添加外键：

ALTER TABLE table_name
ADD FOREIGN KEY (column1) REFERENCES parent_table(parent_column);

级联操作：

  • ON DELETE CASCADE：当父表中的记录被删除时，子表中相关的记录也会自动删除。
  • ON UPDATE CASCADE：当父表中的主键值被更新时，子表中相关的外键值也会自动更新。
    注意：
    如果主表设置了外键，建表时没有指定级联操作。Mysql默认不允许删除或更新主表中的外键，会直接报出外键的错误提示。
    级联示例：
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(id)ON DELETE CASCADEON UPDATE CASCADE
);

注意事项：

  • 外键列的数据类型必须与引用的主键列的数据类型兼容。
  • 外键约束会增加一定的性能开销，尤其是在大规模数据操作时。

3、唯一约束（Unique Constraint）

作用：

  • 唯一性：确保列中的所有值都是唯一的，但允许包含NULL值（多NULL值被视为不同的值）。

示例：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE, – 确保每个用户的电子邮件地址是唯一的，但可以为空
username VARCHAR(50)
);
解释：

  • email列上的唯一约束确保每个用户的电子邮件地址是唯一的。

或者在创建表后添加唯一约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1);

复合唯一约束：

  • 可以为多个列组合定义唯一约束，确保这些列的组合值是唯一的。
    示例：
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),UNIQUE (first_name, last_name)  -- 确保第一名称和姓氏的组合是唯一的
);

注意事项：

  • 唯一约束允许NULL值，但多个NULL值被视为不同的值。
  • 如果需要确保某一列既唯一又不允许NULL，可以同时使用NOT NULL和UNIQUE约束。

4、非空约束（Not Null Constraint）

作用：

  • 强制非空：确保列中的值不能为空（即不允许NULL）。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,  -- 确保员工的名字不能为空age INT
);

解释：

  • name列上的NOT NULL约束确保每个员工的名字不能为空。

或者在创建表后修改列以添加非空约束：

ALTER TABLE table_name
MODIFY column1 datatype NOT NULL;

注意事项：

  • 非空约束不能与AUTO_INCREMENT一起使用，因为AUTO_INCREMENT会自动为列生成值。
  • 非空约束适用于所有数据类型，包括数值、字符串、日期等。

5、默认值约束（Default Constraint）

作用：

  • 设置默认值：当插入新记录时，如果没有为某列提供值，则自动使用指定的默认值。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT DEFAULT 18,  -- 如果没有提供年龄，默认为 18hire_date DATE DEFAULT CURRENT_DATE  -- 如果没有提供入职日期，默认为当前日期
);

解释：

  • age列上的默认值为18，如果插入新记录时未提供年龄，则自动使用18。
  • hire_date列上的默认值为CURRENT_DATE，表示当前日期。

或者在创建表后修改列以添加默认值：

ALTER TABLE table_name
ALTER COLUMN column1 SET DEFAULT default_value;

注意事项：

  • 默认值可以是常量、表达式或函数（如CURRENT_DATE）。
  • 默认值不会覆盖显式提供的值，只有在插入时未提供值时才会使用默认值。

6、检查约束（Check Constraint）

作用：

  • 数据验证：确保列中的值满足特定的条件或范围。只有符合条件的值才能插入或更新到该列中。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT CHECK (age >= 18 AND age <= 65),  -- 确保年龄在 18 到 65 之间salary DECIMAL(10, 2) CHECK (salary > 0)  -- 确保工资大于 0
);

解释：

  • age列上的检查约束确保年龄在18到65之间。
  • salary列上的检查约束确保工资大于0。

或者在创建表后添加检查约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

注意事项：

  • 检查约束可以包含简单的条件（如 >=、<=、=）或更复杂的表达式。
  • 检查约束只适用于单个列，不能跨多个列进行复杂检查。
  • 在某些版本的MySQL中（如5.7及之前），检查约束虽然可以定义，但并不会实际执行。从MySQL 8.0.16开始，检查约束得到了完整的支持并会强制执行。

7、枚举约束（Enum Constraint）

作用：

  • 限制取值范围：枚举类型允许你为列定义一组固定的、预定义的值。插入或更新时，列的值必须是这些预定义值之一。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,status ENUM('active', 'inactive', 'on_leave') NOT NULL  -- 状态只能是 'active'、'inactive' 或'on_leave'
);

解释：

  • status列上的枚举约束确保状态只能是active、inactive或on_leave。

注意事项：

  • 枚举值是按定义顺序存储的，内部使用整数索引表示。例如，'active’对应索引1，‘inactive’ 对应索引2，依此类推。
  • 枚举类型适合用于有限的、固定的选择集，如性别、状态等。

8、集合约束（Set Constraint）

作用：

  • 多值选择：集合类型允许你为列定义一组固定的、预定义的值，并且该列可以存储多个值。每个值必须是这些预定义值之一。

示例：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,skills SET('Java', 'Python', 'C++', 'SQL') NOT NULL  -- 技能可以是多个值的组合
);

解释：

  • skills列上的集合约束允许存储多个技能，如’Java,Python’或’C++,SQL’。

注意事项：

  • 集合类型适合用于多选一或多选多的情况，如技能、权限等。
  • 集合值是按位存储的，内部使用位掩码表示。例如，'Java’对应位1，'Python’对应位2，依此类推。

9、复合约束

在MySQL中，可以为一个字段定义多个约束。

示例：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(100) UNIQUE NOT NULL  -- 唯一 + 非空
);

解释： 唯一（Unique）+非空（NOT NULL）

  • 唯一约束：确保该字段中的所有值都是唯一的，但允许NULL值（多个NULL值被视为不同的值）。
  • 非空约束：确保该字段不允许NULL值。
    所以这两个叠加在一起约束，就起到了唯一且不允许为null的约束。

在MySQL中，可以为一个字段定义多个约束.
常见的组合包括：

  • 主键 + 非空
  • 唯一 + 非空
  • 外键 + 非空
  • 默认值 + 非空
  • 检查 + 非空
  • 枚举 + 非空
  • 集合 + 非空
  • 唯一 + 默认值
  • 外键 + 默认值
  • 唯一 + 检查
  • 外键 + 检查
    通过合理组合多个约束，你可以更精确地控制字段的值，确保数据的完整性和一致性。然而，需要注意的是，某些约束组合可能会相互影响，因此在设计表结构时应仔细考虑每个约束的作用和限制。
    所以通常不使用复合约束，除非必要场景。

10、总结

MySQL提供了多种类型的约束来确保数据的完整性和一致性。

适用场景：

  • 主键约束：确保每一行都有唯一的标识符。
  • 外键约束：维护表之间的引用完整性。
  • 唯一约束：确保列中的值是唯一的。
  • 非空约束：确保列中的值不能为空。
  • 默认值约束：为列提供默认值。
  • 检查约束：确保列中的值满足特定条件。
  • 枚举约束：限制列的取值范围为预定义的值。
  • 集合约束：允许列存储多个预定义的值。

通过合理使用这些约束，你可以有效地保护数据库中的数据，防止不合法的数据进入系统，从而提高数据的质量和可靠性。

乘风破浪会有时，直挂云帆济沧海！！！


http://www.ppmy.cn/embedded/153690.html

相关文章

stable diffusion 量化学习笔记

stable diffusion 量化学习笔记

文章目录 一、一些tensorRT背景及使用介绍1&#xff09;深度学习介绍2&#xff09;TensorRT优化策略介绍3&#xff09;TensorRT基础使用流程4&#xff09;dynamic shape 模式5&#xff09;TensorRT模型转换 二、TensorRT转onnx模型1&#xff09;onnx介绍2&#xff09;背景知识&…
阅读更多...
fitz获取pdf内容

fitz获取pdf内容

1.获取pdf单页&#xff0c;及所有内容 import fitz # PyMuPDF# 打开 PDF 文件 pdf_path r"/data2/ljsang/0106/0725_Self-organization-of-plasticity-and-specialization-in-a-primi_2022_Cell-Syst.pdfπσΣ╕╖σσ║Θ║∩╝Φ╛τ▒│σ¡τ╛π.pdf" d…
阅读更多...
【STM32+QT项目】基于STM32与QT的智慧粮仓环境监测与管理系统设计（完整工程资料源码）

【STM32+QT项目】基于STM32与QT的智慧粮仓环境监测与管理系统设计（完整工程资料源码）

视频演示： 基于STM32与QT的智慧粮仓环境监测与管理系统设计 目录： 目录 视频演示： 目录： 前言：
阅读更多...
【目标检测】数据集合集！

【目标检测】数据集合集！

本文将为您介绍经典、热门的数据集&#xff0c;希望对您在选择适合的数据集时有所帮助。 1 objectdetection_script 更新时间&#xff1a;2025-01-06 访问地址: GitHub 描述&#xff1a; 这个项目主要是提供一些关于目标检测的代码和改进思路参考。主要分为几个方面&#xf…
阅读更多...
openwrt 常见编译问题及编译提速

openwrt 常见编译问题及编译提速

目录 friendlywrt介绍官方编译流程编译问题git clone errorbusybox和其他包冲突GCC编译错误ERROR: package/feeds/packages/gcc failed to build.手动编译编译提速ccache修改makefile去掉一些不必要的依赖固件定制：增加已经编译好的应用rootfs生成过程REF本文以friendlywrt22-…
阅读更多...
用 Python 从零开始创建神经网络（十九）：真实数据集

用 Python 从零开始创建神经网络（十九）：真实数据集

真实数据集 引言数据准备数据加载数据预处理数据洗牌批次&#xff08;Batches&#xff09;训练&#xff08;Training&#xff09;到目前为止的全部代码&#xff1a; 引言 在实践中&#xff0c;深度学习通常涉及庞大的数据集&#xff08;通常以TB甚至更多为单位&#xff09;&am…
阅读更多...
LeetCode 热题 100_二叉树的最近公共祖先（48_236_中等_C++）(二叉树；深度优先搜索)

LeetCode 热题 100_二叉树的最近公共祖先（48_236_中等_C++）(二叉树；深度优先搜索)

LeetCode 热题 100_二叉树的最近公共祖先&#xff08;48_236&#xff09; 题目描述&#xff1a;输入输出样例&#xff1a;题解&#xff1a;解题思路&#xff1a;思路一&#xff08;深度优先搜索&#xff09;&#xff1a; 代码实现代码实现&#xff08;思路一&#xff08;深度优…
阅读更多...
大语言模型训练

大语言模型训练

步骤 Self-Supervised Pre-Training&#xff0c;简称SPTSupervised Fine-Tuning&#xff0c;简称SFTLearning from Human Feedback&#xff0c;简称LfHF Self-Supervised Pre-Training 自监督预训练&#xff08;Self-Supervised Pre-Training&#xff0c;简称SPT&#xff09…
阅读更多...
最新文章

Copyright @ 2022~2023