一、基本概念定义约束是数据库中用于限制表数据的规则通过约束可以强制保证数据的正确性、有效性和完整性防止非法数据进入数据库。作用保证数据完整性确保数据符合业务逻辑如主键唯一标识、外键关联一致性防止错误数据限制字段值范围如非空、唯一、检查约束避免无效数据如空值、重复值维护数据一致性通过外键约束保证表与表之间的关联关系如子表的外键必须对应父表的主键。二、约束的分类根据约束的功能可分为6类对应不同的关键字约束类型描述关键字非空约束限制字段数据不能为null必须提供值。NOT NULL唯一约束保证字段所有数据唯一、不重复允许一个null但null不视为重复。UNIQUE主键约束一行数据的唯一标识要求非空且唯一一个表只能有一个主键。PRIMARY KEY默认约束保存数据时若未指定字段值则采用默认值如DEFAULT 0。DEFAULT检查约束MySQL 8.0.16保证字段值满足特定条件如CHECK (age 0)。CHECK外键约束让两张表建立连接保证数据一致性和完整性子表的外键对应父表的主键。FOREIGN KEY三、外键约束的删除更新行为外键约束的删除/更新行为ON DELETE/ON UPDATE用于定义父表记录变更时子表对应外键记录的处理方式。常见行为如下1、NO ACTION或RESTRICT说明当父表删除/更新记录时先检查子表是否有对应外键。若有则不允许删除/更新父表记录阻止操作。区别NO ACTION是SQL标准行为RESTRICT是MySQL的别名两者完全一致MySQL中无实际区别。2、CASCADE级联说明当父表删除/更新记录时自动删除/更新子表中对应的外键记录。示例父表parent的主键id1被删除子表child中外键parent_id1的记录会被自动删除若父表id1更新为id2子表parent_id1会被自动更新为2。3、SET NULL设为空说明当父表删除记录时自动将子表中对应的外键值设为null要求外键字段允许null否则报错。注意SET NULL仅适用于删除操作更新操作不支持因为更新父表主键时子表外键应改为新值而非null。4、SET DEFAULT设为默认值说明当父表变更时子表外键列设为默认值如DEFAULT 0。限制MySQL的InnoDB引擎不支持此行为仅MyISAM支持但MyISAM无外键约束故实际无意义。四、约束管理1、查看现有约束在修改之前你必须先知道表里有什么约束以及它们叫什么名字特别是外键和索引名。命令SHOW CREATE TABLE表名;场景 你接手了一个老项目想知道 orders 表里的外键到底叫什么名字或者想确认某个字段有没有加唯一索引。-- 查看 orders 表的完整建表语句包含所有约束定义SHOWCREATETABLEorders;输出结果中CONSTRAINT fk_xxx FOREIGN KEY...这一行里的fk_xxx就是外键的逻辑名称删除时必须用它。而UNIQUE KEY uk_xxx里的uk_xxx是唯一索引的名称。2、添加新约束当业务规则变严格时例如以前允许邮箱为空现在必须必填且唯一我们需要添加约束。添加非空/默认/检查约束这类约束直接依附于字段使用MODIFY修改字段定义即可。-- 给 email 字段添加非空约束和默认值ALTERTABLEusersMODIFYCOLUMNemailVARCHAR(100)NOTNULLDEFAULTunknownexample.com;添加主键、唯一、外键约束这类约束通常作为表级对象存在。-- 1. 添加唯一约束 (假设之前没加)ALTERTABLEusersADDUNIQUE(phone_number);-- 2. 添加外键约束 (最常用)ALTERTABLEordersADDCONSTRAINTfk_order_userFOREIGNKEY(user_id)REFERENCESusers(id);ADD CONSTRAINT后面的名字如fk_order_user是你自己起的建议遵循fk_子表_父表的命名规范方便日后维护。3、移除约束当业务逻辑变更或者为了优化写入性能高并发系统常去掉数据库外键需要移除约束。删除非空/默认约束同样通过MODIFY字段定义来“覆盖”旧规则。-- 去掉 status 字段的非空约束允许为空ALTERTABLEusersMODIFYCOLUMNstatusTINYINTNULL;删除主键、唯一、外键约束这里要注意语法的区别删除主键 一个表只有一个主键不需要名字。ALTERTABLEusersDROPPRIMARYKEY;删除唯一约束 实际上删的是对应的索引。-- 这里的 uk_phone 是索引名不是字段名ALTERTABLEusersDROPINDEXuk_phone;删除外键约束 必须指定外键名称。ALTERTABLEordersDROPFOREIGNKEYfk_order_user;删除外键后如果该外键字段上自动生成的索引也不再需要记得手动 DROP INDEX 把它也删掉否则它会继续占用空间并影响写入速度。4、修改约束MySQL 没有ALTER CONSTRAINT语法。“改” “先删后加”。场景举例原本订单表的外键策略是“禁止删除用户RESTRICT”现在业务变更为“删除用户时将其订单归属置空SET NULL”。-- 第一步查名字如果不确定SHOWCREATETABLEorders;-- 假设查到外键名叫 fk_order_user-- 第二步删掉旧的外键约束ALTERTABLEordersDROPFOREIGNKEYfk_order_user;-- 第三步加上新的外键约束带新的级联策略ALTERTABLEordersADDCONSTRAINTfk_order_user_new-- 建议换个新名字或者沿用旧名FOREIGNKEY(user_id)REFERENCESusers(id)ONDELETESETNULL;五、约束设计最佳实践主键选择优先使用无业务含义的自增整数如AUTO_INCREMENT避免因业务变化而修改主键。外键命名使用fk_子表_父表的命名规范便于理解和维护。检查约束在 MySQL 8.0.16 及以上版本中积极使用将数据验证逻辑下沉到数据库层。性能考量外键约束会带来一定的性能开销在写入频繁的超大型表中需谨慎评估。但通常其带来的数据一致性保障远大于性能损失。业务匹配根据业务逻辑仔细选择ON DELETE和ON UPDATE行为例如核心业务数据关联如订单-用户使用RESTRICT或CASCADE。日志、历史记录等可独立存在的数据关联可使用SET NULL。六、综合示例场景背景电商系统的“用户”与“订单”假设我们正在为一个电商平台设计数据库。这里有两个核心角色用户表 (users)也就是“父表”。订单表 (orders)也就是“子表”。业务逻辑是一个用户可以下多个订单但一个订单必须属于某一个用户。如果用户注销了他的订单该怎么处理这就是我们要通过约束来解决的问题。1、建表实战综合应用约束请看下面的 SQL 代码我会在代码注释中为你拆解每一个约束的作用-- 1. 创建父表用户表CREATETABLEusers(user_idINTPRIMARYKEY,-- 【主键约束】用户的唯一身份证非空且唯一usernameVARCHAR(50)NOTNULL,-- 【非空约束】注册时必须填名字不能留空emailVARCHAR(100)UNIQUE,-- 【唯一约束】邮箱不能重复防止两人用同一个邮箱注册statusTINYINTDEFAULT1-- 【默认约束】如果不填状态默认为1代表“正常”);-- 2. 创建子表订单表CREATETABLEorders(order_idINTPRIMARYKEY,-- 【主键约束】订单号唯一user_idINT,-- 这是一个普通字段准备用来做外键amountDECIMAL(10,2)CHECK(amount0),-- 【检查约束】订单金额必须大于0防止录入负数金额create_timeDATETIME,-- 【外键约束】核心登场CONSTRAINTfk_user_order-- 给这个外键关系起个名字方便管理FOREIGNKEY(user_id)-- 子表的哪个字段是外键是 user_idREFERENCESusers(user_id)-- 它关联的是父表(users)的哪个字段是 user_idONDELETECASCADE-- 【删除行为】父表删人子表订单跟着删级联删除ONUPDATECASCADE-- 【更新行为】父表改ID子表订单跟着改级联更新);2、深度解析外键的删除与更新行为在这个例子中我们在 orders 表设置了 ON DELETE CASCADE 和 ON UPDATE CASCADE。让我们看看在实际操作中会发生什么神奇的事情。场景 A正常的插入检查约束与非空约束-- 插入一个正常用户INSERTINTOusers(user_id,username,email)VALUES(1,张三,zhangtest.com);-- 插入一个订单金额为 100INSERTINTOorders(order_id,user_id,amount)VALUES(1001,1,100.00);-- 成功因为用户1存在且金额大于0。-- 尝试插入一个金额为 -50 的订单INSERTINTOorders(order_id,user_id,amount)VALUES(1002,1,-50.00);-- 报错违反了 CHECK (amount 0) 检查约束。场景 B外键的“连坐”机制CASCADE这是外键最强大的地方。注意看我们设置的 ON DELETE CASCADE级联删除。操作 管理员决定封禁并删除“张三”这个用户。DELETEFROMusersWHEREuser_id1;结果users 表中 user_id 1 的记录被删除了。关键点来了MySQL 会自动去 orders 表里找发现订单 1001 是属于用户 1 的。因为设置了 CASCADE订单 1001 也会被自动删除场景 C如果想保留订单怎么办SET NULL如果在实际业务中即便用户注销了我们也想保留他的订单记录用于财务审计我们就不能用 CASCADE 了而应该在建表时使用 ON DELETE SET NULL。假设我们修改了外键策略为 SET NULL操作 再次删除用户 1。DELETEFROMusersWHEREuser_id1;结果users 表中用户 1 没了。orders 表中订单 1001 依然存在但是订单 1001 的 user_id 字段变成了 NULL。注意如果要使用 SET NULL你的子表外键字段user_id必须允许为 NULL即建表时不能加 NOT NULL。
一、基本概念定义约束是数据库中用于限制表数据的规则通过约束可以强制保证数据的正确性、有效性和完整性防止非法数据进入数据库。作用保证数据完整性确保数据符合业务逻辑如主键唯一标识、外键关联一致性防止错误数据限制字段值范围如非空、唯一、检查约束避免无效数据如空值、重复值维护数据一致性通过外键约束保证表与表之间的关联关系如子表的外键必须对应父表的主键。二、约束的分类根据约束的功能可分为6类对应不同的关键字约束类型描述关键字非空约束限制字段数据不能为null必须提供值。NOT NULL唯一约束保证字段所有数据唯一、不重复允许一个null但null不视为重复。UNIQUE主键约束一行数据的唯一标识要求非空且唯一一个表只能有一个主键。PRIMARY KEY默认约束保存数据时若未指定字段值则采用默认值如DEFAULT 0。DEFAULT检查约束MySQL 8.0.16保证字段值满足特定条件如CHECK (age 0)。CHECK外键约束让两张表建立连接保证数据一致性和完整性子表的外键对应父表的主键。FOREIGN KEY三、外键约束的删除更新行为外键约束的删除/更新行为ON DELETE/ON UPDATE用于定义父表记录变更时子表对应外键记录的处理方式。常见行为如下1、NO ACTION或RESTRICT说明当父表删除/更新记录时先检查子表是否有对应外键。若有则不允许删除/更新父表记录阻止操作。区别NO ACTION是SQL标准行为RESTRICT是MySQL的别名两者完全一致MySQL中无实际区别。2、CASCADE级联说明当父表删除/更新记录时自动删除/更新子表中对应的外键记录。示例父表parent的主键id1被删除子表child中外键parent_id1的记录会被自动删除若父表id1更新为id2子表parent_id1会被自动更新为2。3、SET NULL设为空说明当父表删除记录时自动将子表中对应的外键值设为null要求外键字段允许null否则报错。注意SET NULL仅适用于删除操作更新操作不支持因为更新父表主键时子表外键应改为新值而非null。4、SET DEFAULT设为默认值说明当父表变更时子表外键列设为默认值如DEFAULT 0。限制MySQL的InnoDB引擎不支持此行为仅MyISAM支持但MyISAM无外键约束故实际无意义。四、约束管理1、查看现有约束在修改之前你必须先知道表里有什么约束以及它们叫什么名字特别是外键和索引名。命令SHOW CREATE TABLE表名;场景 你接手了一个老项目想知道 orders 表里的外键到底叫什么名字或者想确认某个字段有没有加唯一索引。-- 查看 orders 表的完整建表语句包含所有约束定义SHOWCREATETABLEorders;输出结果中CONSTRAINT fk_xxx FOREIGN KEY...这一行里的fk_xxx就是外键的逻辑名称删除时必须用它。而UNIQUE KEY uk_xxx里的uk_xxx是唯一索引的名称。2、添加新约束当业务规则变严格时例如以前允许邮箱为空现在必须必填且唯一我们需要添加约束。添加非空/默认/检查约束这类约束直接依附于字段使用MODIFY修改字段定义即可。-- 给 email 字段添加非空约束和默认值ALTERTABLEusersMODIFYCOLUMNemailVARCHAR(100)NOTNULLDEFAULTunknownexample.com;添加主键、唯一、外键约束这类约束通常作为表级对象存在。-- 1. 添加唯一约束 (假设之前没加)ALTERTABLEusersADDUNIQUE(phone_number);-- 2. 添加外键约束 (最常用)ALTERTABLEordersADDCONSTRAINTfk_order_userFOREIGNKEY(user_id)REFERENCESusers(id);ADD CONSTRAINT后面的名字如fk_order_user是你自己起的建议遵循fk_子表_父表的命名规范方便日后维护。3、移除约束当业务逻辑变更或者为了优化写入性能高并发系统常去掉数据库外键需要移除约束。删除非空/默认约束同样通过MODIFY字段定义来“覆盖”旧规则。-- 去掉 status 字段的非空约束允许为空ALTERTABLEusersMODIFYCOLUMNstatusTINYINTNULL;删除主键、唯一、外键约束这里要注意语法的区别删除主键 一个表只有一个主键不需要名字。ALTERTABLEusersDROPPRIMARYKEY;删除唯一约束 实际上删的是对应的索引。-- 这里的 uk_phone 是索引名不是字段名ALTERTABLEusersDROPINDEXuk_phone;删除外键约束 必须指定外键名称。ALTERTABLEordersDROPFOREIGNKEYfk_order_user;删除外键后如果该外键字段上自动生成的索引也不再需要记得手动 DROP INDEX 把它也删掉否则它会继续占用空间并影响写入速度。4、修改约束MySQL 没有ALTER CONSTRAINT语法。“改” “先删后加”。场景举例原本订单表的外键策略是“禁止删除用户RESTRICT”现在业务变更为“删除用户时将其订单归属置空SET NULL”。-- 第一步查名字如果不确定SHOWCREATETABLEorders;-- 假设查到外键名叫 fk_order_user-- 第二步删掉旧的外键约束ALTERTABLEordersDROPFOREIGNKEYfk_order_user;-- 第三步加上新的外键约束带新的级联策略ALTERTABLEordersADDCONSTRAINTfk_order_user_new-- 建议换个新名字或者沿用旧名FOREIGNKEY(user_id)REFERENCESusers(id)ONDELETESETNULL;五、约束设计最佳实践主键选择优先使用无业务含义的自增整数如AUTO_INCREMENT避免因业务变化而修改主键。外键命名使用fk_子表_父表的命名规范便于理解和维护。检查约束在 MySQL 8.0.16 及以上版本中积极使用将数据验证逻辑下沉到数据库层。性能考量外键约束会带来一定的性能开销在写入频繁的超大型表中需谨慎评估。但通常其带来的数据一致性保障远大于性能损失。业务匹配根据业务逻辑仔细选择ON DELETE和ON UPDATE行为例如核心业务数据关联如订单-用户使用RESTRICT或CASCADE。日志、历史记录等可独立存在的数据关联可使用SET NULL。六、综合示例场景背景电商系统的“用户”与“订单”假设我们正在为一个电商平台设计数据库。这里有两个核心角色用户表 (users)也就是“父表”。订单表 (orders)也就是“子表”。业务逻辑是一个用户可以下多个订单但一个订单必须属于某一个用户。如果用户注销了他的订单该怎么处理这就是我们要通过约束来解决的问题。1、建表实战综合应用约束请看下面的 SQL 代码我会在代码注释中为你拆解每一个约束的作用-- 1. 创建父表用户表CREATETABLEusers(user_idINTPRIMARYKEY,-- 【主键约束】用户的唯一身份证非空且唯一usernameVARCHAR(50)NOTNULL,-- 【非空约束】注册时必须填名字不能留空emailVARCHAR(100)UNIQUE,-- 【唯一约束】邮箱不能重复防止两人用同一个邮箱注册statusTINYINTDEFAULT1-- 【默认约束】如果不填状态默认为1代表“正常”);-- 2. 创建子表订单表CREATETABLEorders(order_idINTPRIMARYKEY,-- 【主键约束】订单号唯一user_idINT,-- 这是一个普通字段准备用来做外键amountDECIMAL(10,2)CHECK(amount0),-- 【检查约束】订单金额必须大于0防止录入负数金额create_timeDATETIME,-- 【外键约束】核心登场CONSTRAINTfk_user_order-- 给这个外键关系起个名字方便管理FOREIGNKEY(user_id)-- 子表的哪个字段是外键是 user_idREFERENCESusers(user_id)-- 它关联的是父表(users)的哪个字段是 user_idONDELETECASCADE-- 【删除行为】父表删人子表订单跟着删级联删除ONUPDATECASCADE-- 【更新行为】父表改ID子表订单跟着改级联更新);2、深度解析外键的删除与更新行为在这个例子中我们在 orders 表设置了 ON DELETE CASCADE 和 ON UPDATE CASCADE。让我们看看在实际操作中会发生什么神奇的事情。场景 A正常的插入检查约束与非空约束-- 插入一个正常用户INSERTINTOusers(user_id,username,email)VALUES(1,张三,zhangtest.com);-- 插入一个订单金额为 100INSERTINTOorders(order_id,user_id,amount)VALUES(1001,1,100.00);-- 成功因为用户1存在且金额大于0。-- 尝试插入一个金额为 -50 的订单INSERTINTOorders(order_id,user_id,amount)VALUES(1002,1,-50.00);-- 报错违反了 CHECK (amount 0) 检查约束。场景 B外键的“连坐”机制CASCADE这是外键最强大的地方。注意看我们设置的 ON DELETE CASCADE级联删除。操作 管理员决定封禁并删除“张三”这个用户。DELETEFROMusersWHEREuser_id1;结果users 表中 user_id 1 的记录被删除了。关键点来了MySQL 会自动去 orders 表里找发现订单 1001 是属于用户 1 的。因为设置了 CASCADE订单 1001 也会被自动删除场景 C如果想保留订单怎么办SET NULL如果在实际业务中即便用户注销了我们也想保留他的订单记录用于财务审计我们就不能用 CASCADE 了而应该在建表时使用 ON DELETE SET NULL。假设我们修改了外键策略为 SET NULL操作 再次删除用户 1。DELETEFROMusersWHEREuser_id1;结果users 表中用户 1 没了。orders 表中订单 1001 依然存在但是订单 1001 的 user_id 字段变成了 NULL。注意如果要使用 SET NULL你的子表外键字段user_id必须允许为 NULL即建表时不能加 NOT NULL。
)
