当前位置: 首页 > news >正文

MySQL笔记---表的约束 - 实践

MySQL笔记---表的约束 - 实践

在 MySQL 中,表的约束(Constraint) 是用于限制表中数据存储规则的机制,其核心目的是保证数据的完整性、一致性和有效性,避免无效、重复或逻辑错误的数据进入数据库。

1. 常用约束

1.1 主键约束(PRIMARY KEY)

核心作用唯一标识表中的每一条记录(相当于 “身份证”),确保数据不重复且非空。

  • 一个表只能有 1 个主键(可由单字段或多字段组合而成,即 “复合主键”);
  • 主键字段不允许为 NULL,且值必须唯一

(1)创建表时添加单字段主键

CREATE TABLE 表名称 (列名 类型 [其他约束] PRIMARY KEY,-- 其他列...
);
-- 学生表:以student_id作为主键(唯一标识学生)
CREATE TABLE student (student_id INT PRIMARY KEY,  -- 主键约束student_name VARCHAR(50) NOT NULL,age INT
);

(2)创建表时添加复合主键(多字段组合)

适用于 “单字段无法唯一标识记录” 的场景(如 “选课表”,需 “学生 ID + 课程 ID” 共同唯一标识一条选课记录):

CREATE TABLE 表名称 (列名1 类型 [其他约束],列名2 类型 [其他约束],-- 其他列...PRIMARY KEY (列名1, 列名2, ...)-- 或 CONSTRAINT 约束名 PRIMARY KEY (列名1, 列名2, ...)
);
-- 选课表:复合主键(student_id + course_id)
CREATE TABLE student_course (student_id INT NOT NULL,course_id INT NOT NULL,score INT,-- 复合主键约束(需单独声明)PRIMARY KEY (student_id, course_id)
);

(3)修改表时添加主键

ALTER TABLE 表名称 ADD PRIMARY KEY (列名1, 列名2, ...);
-- 为已存在的表添加主键(前提:该列无NULL值且唯一)
ALTER TABLE student ADD PRIMARY KEY (student_id);

(3)删除主键

-- 每个表最多只能有一个主键, 无需指定约束名称
ALTER TABLE 表名称 DROP PRIMARY KEY;

1.2 唯一键约束(UNIQUE)

核心作用:确保字段的值在表中唯一(不重复),但允许为 NULL(且仅允许 1 个 NULL 值)。

  • 一个表可以有多个唯一约束;
  • 与主键的区别:唯一约束允许 NULL,主键不允许;一个表仅 1 个主键,但可多个唯一约束。

(1)创建表时添加唯一约束

CREATE TABLE 表名称 (列名 类型 [其他约束] UNIQUE,-- 其他列...
);
CREATE TABLE 表名称 (列名 类型 [其他约束],-- 其他列...UNIQUE (列名, ...)-- 或 CONSTRAINT 约束名 UNIQUE (列名, ...)
);
-- 学生表:student_id为主键,student_phone唯一(不重复手机号)
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50) NOT NULL,student_phone VARCHAR(20) UNIQUE,  -- 唯一约束(手机号不重复)age INT
);

(2)修改表时添加唯一约束

ALTER TABLE 表名称 ADD UNIQUE (列名1, 列名2, ...);
ALTER TABLE student ADD UNIQUE (student_phone);

(3)删除唯一约束

需通过 "约束名" 删除(若未指定约束名,MySQL会自动生成,可通过SHOW CREATE TABLE查看)

ALTER TABLE 表名称 DROP CONSTRAINT 约束名;
ALTER TABLE test DROP CONSTRAINT id;

1.3 外键约束(FOREIGN KEY)

核心作用:建立两个表之间的关联关系(通常是 “主表” 和 “从表”),确保从表的关联字段值必须在主表的参照字段中存在(避免 “孤儿数据”)。

  • 主表(参照表):被参照的表(如 “班级表”),参照字段必须是主表的主键或唯一键;
  • 从表(被参照表):依赖主表的表(如 “学生表”),外键字段关联主表的参照字段;
  • 外键约束会强制 “从表数据必须依赖主表存在”(如:不能添加一个不存在的班级 ID 的学生)。

当主表的参照字段值被修改 / 删除时,从表的外键字段如何处理?可通过ON UPDATE和ON DELETE指定级联策略

级联策略作用(以主表删除记录为例)
ON DELETE RESTRICT禁止删除(若从表有关联数据,主表无法删除,默认策略)
ON DELETE CASCADE级联删除(主表删除记录,从表关联记录也自动删除)
ON DELETE SET NULL置空(主表删除记录,从表关联字段设为 NULL,需外键允许 NULL)

(1)创建主表和从表(含外键约束)

CREATE TABLE 从表名称 (与主表关联的列 类型 [其他约束],FOREIGN KEY (与主表关联的列)REFERENCES 主表名称 (主表的对应列)[级联策略]-- 或 CONSTRAINT 约束名 FOREIGN KEY (与主表关联的列)--    REFERENCES 主表名称 (主表的对应列)--    [级联策略]
);
-- 1. 主表:班级表(class_id为主键)
CREATE TABLE class (class_id INT PRIMARY KEY,class_name VARCHAR(50) NOT NULL
);
-- 2. 从表:学生表(class_id为外键,关联班级表的class_id)
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50) NOT NULL,class_id INT,-- 外键约束:关联班级表的class_id,级联删除/更新FOREIGN KEY (class_id)REFERENCES class (class_id)ON DELETE CASCADE  -- 主表删除班级,从表关联学生也删除ON UPDATE CASCADE  -- 主表修改班级ID,从表关联ID也更新
);

(2)修改表时添加外键约束

ALTER TABLE 从表名称
ADD FOREIGN KEY (关联列)
REFERENCES 主表名称 (被关联列)
[级联策略];
ALTER TABLE student
ADD FOREIGN KEY (class_id)
REFERENCES class (class_id)
ON DELETE CASCADE;

1.4 非空约束(NOT NULL)

核心作用:强制字段不允许存储 NULL 值(即必须填写具体内容)。

  • 常用于 “必填项”(如姓名、身份证号、订单号等);
  • 若插入数据时未给非空字段赋值,MySQL 会报错。

(1)创建表时添加非空约束

CREATE TABLE 表名称 (列名 类型 [其他约束] NOT NULL
);
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50) NOT NULL,  -- 非空约束(姓名必填)student_phone VARCHAR(20) UNIQUE,age INT  -- 允许为NULL(非必填)
);

(2)修改表时添加 / 取消非空约束

-- 添加非空约束
ALTER TABLE 表名称 MODIFY COLUMN 列名 [类型] [其他约束] NOT NULL;
-- 取消非空约束(改为允许NULL)
ALTER TABLE 表约束 MODIFY COLUMN 列名 [类型] [其他约束] NULL;

1.5 默认值约束(DEFAULT)

核心作用:当插入数据时,若未给该字段赋值,MySQL 会自动填充预设的默认值

  • 默认值可以是常量(如数字、字符串)、函数(如CURDATE()获取当前日期);
  • 若字段同时有NOT NULL和DEFAULT,未赋值时会用默认值填充(避免非空报错)。

(1)创建表时添加默认值约束

CREATE TABLE 表名称 (列名 类型 [其他约束] DEFAULT 默认值
);
-- 订单表:order_status默认值为0(0=待支付,1=已支付),create_time默认当前日期
CREATE TABLE orders (order_id INT PRIMARY KEY,user_id INT NOT NULL,order_status INT DEFAULT 0,  -- 默认值:待支付create_time DATE DEFAULT CURDATE()  -- 默认值:当前系统日期
);

(2)修改表时添加默认值约束

ALTER TABLE 表名称 MOFIFY COLUMN 列名 类型 [其他约束] DEFAULT 默认值;
ALTER TABLE orders MODIFY COLUMN order_status INT DEFAULT 0;

1.6 检查约束(CHECK)

核心作用强制字段值满足指定的条件(如 “年龄必须大于 0”“分数在 0-100 之间”)。

  • 注意:MySQL 5.7 及更早版本会 “忽略” CHECK 约束(语法不报错,但逻辑不生效);MySQL 8.0 及以上版本完全支持CHECK 约束。

(1)创建表时添加检查约束

CREATE TABLE 表名称 (列名 类型 [其他约束] CHECK (条件表达式)
);
-- 学生表:age必须>0且<=150,score必须在0-100之间(MySQL 8.0+生效)
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50) NOT NULL,age INT CHECK (age > 0 AND age <= 150),  -- 检查年龄范围score INT CHECK (score BETWEEN 0 AND 100)  -- 检查分数范围
);

(2)修改表时添加检查约束

ALTER TABLE 表名称 MODIFY COLUMN 列名 类型 [其他约束] CHECK (条件表达式);

2. 其他约束

2.1 自增约束(AUTO_INCREMENT)

虽然AUTO_INCREMENT本质是字段属性,而非严格意义上的 “约束”,但常与主键配合使用,用于自动生成唯一的递增 ID(如订单号、用户 ID),因此在此补充:

  • 仅支持整数类型(INT、BIGINT 等);
  • 一个表只能有 1 个 AUTO_INCREMENT 字段,且该字段通常是主键或唯一键
  • 自增起始值默认从 1 开始,可通过AUTO_INCREMENT = N修改起始值。
CREATE TABLE 表名称 (列名 类型 [其他约束] AUTO_INCREMENT
) AUTO_INCREMENT = 起始值;
-- 学生表:student_id自增(无需手动插入,自动生成1、2、3...)
CREATE TABLE student (student_id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增+主键student_name VARCHAR(50) NOT NULL
);
mysql> INSERT INTO student (student_name) VALUES ('张三');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student (student_name) VALUES ('李四');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student (student_name) VALUES ('王五');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM student;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|          1 | 张三         |
|          2 | 李四         |
|          3 | 王五         |
+------------+--------------+
3 rows in set (0.00 sec)

2.2 列的描述(COMMENT)

列的描述(通过COMMENT关键字定义)是对列的元数据说明,不影响数据存储和约束,仅用于文档化表结构,方便开发者理解列的含义。

-- 创建表时为列添加描述
CREATE TABLE student (student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生唯一标识ID,自增',student_name VARCHAR(50) NOT NULL COMMENT '学生姓名,不能为空',age INT CHECK (age > 0 AND age <= 150) COMMENT '学生年龄,必须在1-150之间',phone VARCHAR(20) UNIQUE COMMENT '学生手机号,唯一不重复'
);
-- 查看列描述(通过表结构信息)
SHOW FULL COLUMNS FROM student;

效果:COMMENT的内容会被存储在数据库元数据中,通过SHOW FULL COLUMNSDESCRIBE可查看,帮助团队理解列的设计意图。

2.3 用0填充(ZEROFILL)

核心作用: 当数值的实际位数 < 显示宽度时,查询结果会在数值前补 0,直至达到指定宽度;若实际位数 ≥ 显示宽度,则按实际数值显示,不截断。

ZEROFILL需与 “显示宽度”(如INT(5)中的5)配合使用,语法格式如下:

列名 类型(显示宽度) ZEROFILL;

示例:

  1. 创建含ZEROFILL的表
    -- 创建表:id字段为INT(5) ZEROFILL(显示宽度5,不足补0)
    CREATE TABLE demo (id INT(5) ZEROFILL,  -- 显示宽度5,启用ZEROFILLnum INT(3) ZEROFILL   -- 显示宽度3,启用ZEROFILL
    );
  2. 插入数据并观察效果
    -- 插入不同长度的数值
    INSERT INTO demo (id, num) VALUES
    (12, 5),        -- 12(2位)、5(1位)
    (12345, 678),   -- 12345(5位)、678(3位)
    (123456, 7890); -- 123456(6位)、7890(4位)
  3. 查询结果
    mysql> SELECT * FROM demo;
    +--------+------+
    | id     | num  |
    +--------+------+
    |  00012 |  005 |
    |  12345 |  678 |
    | 123456 | 7890 |
    +--------+------+
    3 rows in set (0.00 sec)
http://www.hskmm.com/?act=detail&tid=31294

相关文章:

  • 新买的笔记本电脑如何将旧笔记本数据迁移完整迁移克隆过来?买了新电脑,旧电脑大量数据如何迁移?
  • 反射型XSS与自反型XSS深度解析
  • Markdown 是一种「用肉眼就能看懂」标记语言
  • Java 与智慧能源:分布式能源与智能电网管理
  • PHP 真异步 TrueAsync SAPI 与 NGINX Unit 集成
  • GitHub Spark引领Vibe编程与AI技术新趋势
  • Java 与智慧环保:生态监测与低碳治理
  • VMware ESXi 9.0.1.0 macOS Unlocker OEM BIOS 2.7 Huawei 华为 定制版
  • VMware ESXi 9.0.1.0 macOS Unlocker OEM BIOS 2.7 xFusion 超聚变 定制版
  • [AI] AI深度伪造欺诈防范
  • [AI/AI中台] AI应用开发平台:Coze、Dify、阿里百炼、N8N、FastGPT
  • 【GitHub每日速递 251015】爆火, 20k star!小智 AI 聊天机器人多端控制+70 多个开源硬件支持,大模型应用新玩法
  • Voice Agent 开发者第一课:成为进阶语音 AI 玩家,你需要了解这些丨Convo AIRTE2025
  • C++内存管理的那些坑与经验
  • .NET 10 Release Candidate 2(RC2)发布
  • 字节开源 MineContext:截屏+理解上下文;OpenAI 宣布自研 AI 芯片丨日报
  • 读技术之外:社会联结中的人工智能10读后总结与感想兼导读
  • 另一个角度看运放
  • 何时无需AI:数学与统计的实用价值
  • 云防护栏理论:应对云配置错误的安全防护策略
  • 乐理 -07 音程
  • VBA批量设置单元格值和数据有效性
  • 一个关于结构体性能和内存分配的问题
  • 乐理 -07 五线谱
  • CentOS 7.6 环境下基于 Docker 部署 PaddleOCR 源码的实践指南
  • 罗马机场 落地过关 取行李 坐私家车接机攻略
  • LGP10838 [FLA R1] 庭中有奇树 学习笔记
  • 解决Pregenerating ConTeXt MarkIV format. This may take some time...卡死问题
  • 日期相关函数、方法
  • explain