news 2026/5/8 17:25:40

SQL约束

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
SQL约束

数据库基础：SQL 约束

约束（Constraint）是数据库表设计的核心规则，用于强制保证数据的完整性、准确性和一致性，防止脏数据（错误、冗余、矛盾的数据）进入数据库。本文详细讲解 MySQL 中五大核心约束的定义、语法、实战示例、常见错误与最佳实践，覆盖从基础建表到企业级设计的全流程。

一、约束概述

1. 什么是约束

约束是加在数据表列上的强制性规则，数据库会在数据插入、更新、删除时自动检查这些规则，违反规则的操作会被直接拒绝。

  • 核心作用：
    1. 保证数据准确性（如年龄不能为负数）
    2. 保证数据唯一性（如手机号不能重复）
    3. 保证数据完整性（如订单必须关联存在的用户）
    4. 减少应用层代码的校验逻辑

2. MySQL 五大核心约束

约束类型关键字作用
主键约束PRIMARY KEY唯一标识表中的每一行记录，非空且唯一
唯一约束UNIQUE保证某列或某几列的值不重复
非空约束NOT NULL保证某列的值不能为空
默认约束DEFAULT插入数据时未给列赋值，自动使用默认值
外键约束FOREIGN KEY建立两表之间的关联，保证数据一致性

二、主键约束（PRIMARY KEY）

1. 核心特点

  • 非空且唯一：主键列的值不能为 NULL，也不能重复
  • 一个表只能有一个主键（可以是单列主键，也可以是多列联合主键）
  • 主键是表的唯一标识：理论上每张表都必须有主键，用于唯一区分每一行记录
  • 推荐使用自增整数主键：避免业务字段（如手机号、身份证号）作为主键（业务字段可能变更）

2. 自动递增（AUTO_INCREMENT）

MySQL 提供AUTO_INCREMENT关键字，实现主键值的自动增长，无需手动赋值：

  • 只有整数类型的列才能设置为自增列
  • 只有主键列才能设置为自增列
  • 初始化默认值为 1，默认增量为 1
  • 自增列一旦被使用过就不会重复（即使删除了对应记录，下一个值仍会继续递增）

3. 实战语法

（1）创建表时添加主键
-- 方式1：列级定义（推荐，简洁）CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'主键ID',nameVARCHAR(20)NOTNULLCOMMENT'姓名',ageINTCOMMENT'年龄',emailVARCHAR(50)COMMENT'邮箱')COMMENT'学生表';-- 方式2：表级定义（适合联合主键）CREATETABLEstudent(idINTAUTO_INCREMENTCOMMENT'主键ID',nameVARCHAR(20)NOTNULLCOMMENT'姓名',ageINTCOMMENT'年龄',emailVARCHAR(50)COMMENT'邮箱',PRIMARYKEY(id)-- 表级定义主键)COMMENT'学生表';-- 联合主键（多列组合作为主键，保证组合唯一）CREATETABLEstudent_course(student_idINTCOMMENT'学生ID',course_idINTCOMMENT'课程ID',scoreINTCOMMENT'成绩',PRIMARYKEY(student_id,course_id)-- 学生ID+课程ID作为联合主键)COMMENT'学生选课表';
（2）修改表时添加/删除主键
-- 添加主键（表已存在且无主键）ALTERTABLEstudentADDPRIMARYKEY(id);-- 删除主键（注意：如果主键是自增列，需要先取消自增）ALTERTABLEstudentMODIFYidINT;-- 先取消自增ALTERTABLEstudentDROPPRIMARYKEY;-- 再删除主键

4. 常见错误

-- 错误1：插入重复主键INSERTINTOstudent(id,name)VALUES(1,'张三');INSERTINTOstudent(id,name)VALUES(1,'李四');-- 报错：1062 - Duplicate entry '1' for key 'PRIMARY'-- 错误2：主键列插入 NULLINSERTINTOstudent(id,name)VALUES(NULL,'王五');-- 报错：1048 - Column 'id' cannot be null

三、唯一约束（UNIQUE）

1. 核心特点

  • 保证某列或某几列的值不重复
  • 一个表可以有多个唯一约束（主键只能有一个）
  • 唯一约束的列允许为 NULL（且可以有多个 NULL，因为 NULL 不等于 NULL）
  • 常用于约束业务唯一字段：手机号、邮箱、身份证号、用户名等

2. 实战语法

（1）创建表时添加唯一约束
-- 方式1：列级定义CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)NOTNULL,phoneVARCHAR(11)UNIQUECOMMENT'手机号（唯一）',emailVARCHAR(50)UNIQUECOMMENT'邮箱（唯一）')COMMENT'学生表';-- 方式2：表级定义（适合联合唯一约束）CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,class_numVARCHAR(10)COMMENT'班级号',snoVARCHAR(10)COMMENT'学号',nameVARCHAR(20)NOTNULL,UNIQUE(class_num,sno)-- 联合唯一约束：同一个班级内学号唯一)COMMENT'学生表';
（2）修改表时添加/删除唯一约束
-- 添加唯一约束ALTERTABLEstudentADDUNIQUE(email);-- 删除唯一约束（注意：唯一约束会自动创建同名索引，删除索引即删除约束）ALTERTABLEstudentDROPINDEXemail;

3. 常见错误

-- 错误：插入重复的唯一约束列值INSERTINTOstudent(name,email)VALUES('张三','123@qq.com');INSERTINTOstudent(name,email)VALUES('李四','123@qq.com');-- 报错：1062 - Duplicate entry '123@qq.com' for key 'email'-- 正确：唯一约束列可以插入多个 NULLINSERTINTOstudent(name,email)VALUES('王五',NULL);INSERTINTOstudent(name,email)VALUES('赵六',NULL);-- 执行成功

四、非空约束（NOT NULL）

1. 核心特点

  • 强制某列的值不能为空
  • 插入或更新数据时，必须给非空列赋值
  • 常用于必填字段：姓名、手机号、密码等

2. 实战语法

（1）创建表时添加非空约束
CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)NOTNULLCOMMENT'姓名（必填）',ageINTCOMMENT'年龄（可选）',phoneVARCHAR(11)NOTNULLCOMMENT'手机号（必填）')COMMENT'学生表';
（2）修改表时添加/删除非空约束
-- 添加非空约束ALTERTABLEstudentMODIFYageINTNOTNULL;-- 删除非空约束ALTERTABLEstudentMODIFYageINTNULL;

3. 常见错误

-- 错误：非空列插入 NULLINSERTINTOstudent(name,phone)VALUES(NULL,'13800138000');-- 报错：1048 - Column 'name' cannot be null

五、默认约束（DEFAULT）

1. 核心特点

  • 插入数据时，如果未给列赋值，自动使用默认值填充
  • 可以是常量、函数或表达式
  • 常与非空约束配合使用：NOT NULL DEFAULT '默认值'，避免插入错误

2. 实战语法

（1）创建表时添加默认约束
CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)NOTNULL,sexCHAR(1)NOTNULLDEFAULT'男'COMMENT'性别，默认男',ageINTDEFAULT18COMMENT'年龄，默认18',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间，默认当前时间',update_timeDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间，自动更新')COMMENT'学生表';
（2）修改表时添加/删除默认约束
-- 添加默认约束ALTERTABLEstudentALTERCOLUMNageSETDEFAULT20;-- 删除默认约束ALTERTABLEstudentALTERCOLUMNageDROPDEFAULT;

3. 示例效果

-- 插入时不指定 sex、age、create_time，自动使用默认值INSERTINTOstudent(name)VALUES('张三');-- 查询结果：-- id | name | sex | age | create_time | update_time-- 1 | 张三 | 男 | 18 | 2024-05-20 10:00:00 | 2024-05-20 10:00:00-- 更新记录时，update_time 自动更新为当前时间UPDATEstudentSETage=20WHEREid=1;-- update_time 变为 2024-05-20 10:05:00

六、外键约束（FOREIGN KEY）

1. 核心概念

外键用于建立两个表之间的关联关系，保证数据的一致性和完整性。

  • 主表（父表）：被引用的表，提供主键作为外键的参考
  • 从表（子表）：引用主表主键的表，包含外键列
  • 外键列的数据类型必须与主表主键列完全一致
  • 数据库引擎必须为InnoDB（MyISAM 不支持外键）

2. 级联操作（关键！）

当主表的记录被更新或删除时，从表中关联记录的处理方式，通过ON UPDATE和ON DELETE子句指定：

级联选项作用适用场景
CASCADE主表更新/删除记录时，同步更新/删除从表的关联记录强关联关系，如订单和订单项（删除订单自动删除订单项）
SET NULL主表更新/删除记录时，将从表关联列的值设为 NULL（外键列不能为 NOT NULL）弱关联关系，如部门和员工（删除部门后员工部门ID置空）
NO ACTION如果从表有关联记录，不允许主表更新/删除对应记录默认选项，防止误删主表数据
RESTRICT与NO ACTION完全一致，立即检查外键约束同上

3. 实战语法

（1）创建表时添加外键
-- 主表：班级表CREATETABLEclass(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'班级ID',class_nameVARCHAR(50)NOTNULLCOMMENT'班级名')COMMENT'班级表';-- 从表：学生表（引用班级表的id作为外键）CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'学生ID',nameVARCHAR(20)NOTNULLCOMMENT'姓名',class_idINTCOMMENT'班级ID（外键）',-- 定义外键约束FOREIGNKEY(class_id)REFERENCESclass(id)ONUPDATECASCADE-- 主表id更新时，同步更新从表class_idONDELETESETNULL-- 主表记录删除时，从表class_id置为NULL)COMMENT'学生表';
（2）修改表时添加/删除外键
-- 添加外键ALTERTABLEstudentADDCONSTRAINTfk_student_class-- 外键名（建议命名：fk_从表_主表）FOREIGNKEY(class_id)REFERENCESclass(id)ONUPDATECASCADEONDELETESETNULL;-- 删除外键（通过外键名删除）ALTERTABLEstudentDROPFOREIGNKEYfk_student_class;

4. 常见错误

-- 错误1：插入从表记录时，主表不存在对应记录INSERTINTOstudent(name,class_id)VALUES('张三',1);-- 报错：1452 - Cannot add or update a child row: a foreign key constraint fails-- 原因：class表中没有id=1的记录-- 错误2：删除主表记录时，从表有关联记录（级联选项为NO ACTION）INSERTINTOclass(class_name)VALUES('一班');INSERTINTOstudent(name,class_id)VALUES('张三',1);DELETEFROMclassWHEREid=1;-- 报错：1451 - Cannot delete or update a parent row: a foreign key constraint fails

5. 关于外键的争议：为什么很多公司不使用外键？

虽然外键能保证数据一致性，但在企业级开发中，很多团队选择在应用层控制关联关系，而非使用数据库外键，原因如下：

  1. 性能问题：外键会在每次插入、更新、删除时触发约束检查，批量操作时性能损耗明显
  2. 分布式系统限制：分库分表、跨库关联场景下，数据库外键无法使用
  3. 业务灵活性：应用层控制关联关系更灵活，可以实现更复杂的业务逻辑
  4. 数据迁移困难：有外键的表在数据迁移、备份恢复时容易出现约束冲突

最佳实践：

  • 小型项目、低并发场景：可以使用外键，利用数据库保证数据一致性
  • 大型项目、高并发场景：不使用外键，在应用层通过代码控制关联关系和数据一致性

七、约束综合实战：完整表设计示例

下面以用户-订单-订单详情为例，设计包含所有约束的完整表结构：

-- 1. 用户表CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',usernameVARCHAR(20)NOTNULLUNIQUECOMMENT'用户名（唯一）',passwordVARCHAR(32)NOTNULLCOMMENT'密码',phoneVARCHAR(11)NOTNULLUNIQUECOMMENT'手机号（唯一）',emailVARCHAR(50)UNIQUECOMMENT'邮箱（唯一）',statusTINYINTNOTNULLDEFAULT1COMMENT'状态：1-正常，0-禁用',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间')COMMENT'用户表';-- 2. 商品表CREATETABLEproduct(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID',product_nameVARCHAR(100)NOTNULLCOMMENT'商品名',priceDECIMAL(10,2)NOTNULLCOMMENT'价格',stockINTNOTNULLDEFAULT0COMMENT'库存',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间')COMMENT'商品表';-- 3. 订单表（主表）CREATETABLE`order`(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'订单ID',order_noVARCHAR(32)NOTNULLUNIQUECOMMENT'订单号（唯一）',user_idINTNOTNULLCOMMENT'用户ID',total_amountDECIMAL(10,2)NOTNULLCOMMENT'总金额',statusTINYINTNOTNULLDEFAULT0COMMENT'状态：0-待支付，1-已支付，2-已取消',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',-- 外键关联用户表FOREIGNKEY(user_id)REFERENCESuser(id)ONUPDATECASCADEONDELETENOACTION)COMMENT'订单表';-- 4. 订单详情表（从表，关联订单和商品）CREATETABLEorder_item(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'详情ID',order_idINTNOTNULLCOMMENT'订单ID',product_idINTNOTNULLCOMMENT'商品ID',quantityINTNOTNULLCOMMENT'购买数量',priceDECIMAL(10,2)NOTNULLCOMMENT'购买时的单价',-- 联合唯一约束：一个订单中不能有重复的商品UNIQUE(order_id,product_id),-- 外键关联订单表（级联删除：删除订单自动删除订单详情）FOREIGNKEY(order_id)REFERENCES`order`(id)ONUPDATECASCADEONDELETECASCADE,-- 外键关联商品表FOREIGNKEY(product_id)REFERENCESproduct(id)ONUPDATECASCADEONDELETENOACTION)COMMENT'订单详情表';

八、常见问题与最佳实践

1. 常见问题解答

（1）主键和唯一约束的区别？
  • 主键：一个表只能有一个，非空且唯一，用于唯一标识记录
  • 唯一约束：一个表可以有多个，允许为 NULL，用于保证业务字段唯一
（2）自增主键用完了怎么办？
  • 使用BIGINT类型作为主键（范围：-9223372036854775808 到 9223372036854775807），足够绝大多数场景使用
  • 分布式系统使用雪花算法（Snowflake）生成分布式唯一ID
（3）NULL 值有什么问题？
  • NULL 值参与运算时结果永远为 NULL（如NULL + 1 = NULL）
  • NULL 值在COUNT()统计时会被忽略
  • 建议尽量使用非空约束 + 默认值，避免 NULL 值带来的问题
（4）外键和索引的关系？
  • 创建外键时，MySQL 会自动在外键列上创建索引（提高关联查询性能）
  • 删除外键时，需要手动删除对应的索引

2. 企业级最佳实践

  1. 每张表必须有主键：优先使用INT AUTO_INCREMENT或BIGINT AUTO_INCREMENT作为主键
  2. 业务唯一字段必须加唯一约束：如手机号、邮箱、订单号等，防止重复数据
  3. 必填字段加非空约束：避免 NULL 值带来的逻辑错误
  4. 合理使用默认约束：减少插入错误，简化代码
  5. 外键按需使用：小型项目用外键保证一致性，大型高并发项目在应用层控制
  6. 统一命名规范：
    • 主键名：id
    • 外键名：主表名_id（如user_id、order_id）
    • 外键约束名：fk_从表名_主表名（如fk_order_user）
    • 唯一约束名：uk_表名_列名（如uk_user_phone）

九、总结

约束是数据库数据完整性的最后一道防线，合理使用约束能大幅减少脏数据，降低应用层代码的复杂度。掌握五大约束的语法和使用场景，是数据库设计的基础能力。

设计原则：简单清晰、符合业务逻辑、平衡一致性和性能。不要过度使用约束，也不要完全依赖约束，根据项目规模和并发场景选择合适的方案。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/5/8 17:25:16

Windows终极效率革命：PowerToys完全使用指南

Windows终极效率革命：PowerToys完全使用指南 【免费下载链接】PowerToys Microsoft PowerToys is a collection of utilities that supercharge productivity and customization on Windows 项目地址: https://gitcode.com/GitHub_Trending/po/PowerToys 你是…

作者头像 李华
网站建设 2026/5/8 17:25:16

ComfyUI IPAdapter Plus完整教程：3步掌握AI图像引导生成技术

ComfyUI IPAdapter Plus完整教程：3步掌握AI图像引导生成技术 【免费下载链接】ComfyUI_IPAdapter_plus 项目地址: https://gitcode.com/gh_mirrors/co/ComfyUI_IPAdapter_plus 你是否曾经遇到过这样的问题：用AI生成图像时，无论怎么调…

作者头像 李华
网站建设 2026/5/8 17:24:51

AutoSar FiM模块实战：手把手教你配置诊断事件驱动的功能降级

AutoSar FiM模块实战：从零构建诊断事件驱动的功能降级系统 车窗缓缓上升时突然停止，防夹功能意外触发——这可能是电机温度传感器触发了FiM模块的功能降级机制。在汽车电子系统中，这类看似简单的异常背后，隐藏着Dem与FiM模块精密…

作者头像 李华
网站建设 2026/5/8 17:24:29

Windows HEIC缩略图神器：让iPhone照片在资源管理器完美预览

Windows HEIC缩略图神器：让iPhone照片在资源管理器完美预览 【免费下载链接】windows-heic-thumbnails Enable Windows Explorer to display thumbnails for HEIC/HEIF files 项目地址: https://gitcode.com/gh_mirrors/wi/windows-heic-thumbnails 还在为Wi…

作者头像 李华