我翻了翻自己的旧笔记发现上一篇还在讲查询这一篇直接跳到约束感觉有点跳跃。不过约束这东西确实是SQL里绕不开的一道坎建表的时候不把约束想清楚后期填数据、做关联、删数据的时候全是坑。这篇笔记就专门把SQL约束这块从头到尾捋一遍有理论有实操适合正在学SQL的初学者也适合写了好几年SQL但没系统整理过约束规则的开发者拿来当查漏补缺的清单。1. 约束到底在解决什么问题先说个场景。你维护一张用户表手机号列是核心业务字段结果录入的时候有人没填有人填了重复值还有人填了个乱七八糟的格式。等到做用户登录、做消息推送的时候脏数据全冒出来了。约束就是干这个的——提前规定好“什么样的数据才有资格进这张表”。约束本质上是数据库层面的规则引擎它把业务规则下沉到数据存储层而不是依赖上层应用自觉。我见过不少团队把校验逻辑全写在Java或者Python代码里数据库表结构想怎么建就怎么建。早期开发爽了后期数据一多、接口一多每个入口都要写一遍校验漏掉一个就是事故。把能下沉的规则全部下沉到约束是数据一致性投资回报率最高的做法。另一个容易被忽视的点是约束还承担着“数据库自文档化”的职责。一个开发者接手老项目看一遍主键、外键、唯一键、检查约束的声明基本就能猜出这张表的业务含义和关联关系比翻几百行业务代码高效得多。2. 六大约束类型逐个拆解2.1 主键约束PRIMARY KEY每行数据的身份证主键约束要求字段值非空且唯一一张表只能有一个主键但主键可以建立在多个字段上这就是复合主键。我建议绝大多数业务表使用自增整数或者GUID作为主键少用业务字段做主键。比如拿身份证号当用户表主键看着合理但用户一旦变更身份证号现实中真有这种事你就得级联修改所有外键引用。拿订单号这种业务字段做主键也有类似问题业务上看起来唯一不代表永远不会变更规则。复合主键要谨慎使用。两个字段单独看都不唯一合起来才唯一比如订单明细表用“订单ID 商品ID”做复合主键。这个设计能用但后续做关联查询、做ORM映射的时候都会多一层复杂度。能加代理主键的尽量加。CREATE TABLE users ( user_id INT IDENTITY(1,1) PRIMARY KEY, username NVARCHAR(50) NOT NULL );2.2 外键约束FOREIGN KEY表和表之间的契约外键是关系型数据库的灵魂它保证引用完整性。子表的外键值要么是空要么必须在父表的主键中存在。关于外键业界一直有争议。支持的人说外键能保证数据一致性反对的人说外键影响写入性能、在大流量场景下是负担、分布式架构下根本没法用。我的看法是中小型系统、OLTP场景、数据一致性要求高的模块尽管用外键大型分布式系统、分库分表场景外键基本用不了一致性要靠应用层事务和最终一致性方案来保证。但如果你连外键都没搞明白就别谈什么规避了大部分业务系统老老实实建外键更稳妥。CREATE TABLE orders ( order_id INT IDENTITY(1,1) PRIMARY KEY, user_id INT NOT NULL, order_date DATETIME DEFAULT GETDATE(), CONSTRAINT FK_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id) );外键还有一个重要设计决策删除策略。默认RESTRICT有引用则禁止删除可选CASCADE级联删除、SET NULL外键置空。CASCADE听着方便但在生产环境会酿成批量数据误删我见过一次父表删一条记录、级联删掉几千条子表数据的真实事故。慎用CASCADE物理删除场景优先RESTRICT或者软删除方案。SET NULL需要外键列允许NULL适合“保留子表记录但解除关联”的场景比如订单保留但用户被注销。2.3 唯一约束UNIQUE业务上不能重复的硬性要求唯一约束保证一个或多个字段的值在表中不重复。注意它和主键的区别是唯一约束允许NULL值而且SQL Server默认只允许一个NULL。NULL在SQL语义里是“未知”两个NULL不算重复。MySQL的InnoDB允许多个NULL这都不算违反唯一约束。常见业务场景用户名、邮箱、手机号、身份证号、订单流水号。这些字段天然不应该重复数据库层面加唯一索引是最后一道保险应用层就算并发判断也会因为竞态条件漏掉。唯一约束还有个妙用做“幂等”。消息表里加一个“消息ID”的唯一约束重复投递的消息第二次插入直接报错或者被吃掉从而实现消费幂等。很多高并发系统就是这么设计去重机制的。ALTER TABLE users ADD CONSTRAINT UQ_users_phone UNIQUE(phone);2.4 非空约束NOT NULL拒绝缺失值的底线非空约束是最好理解也最容易被忽略的约束。建表时默认所有列都能接受NULL如果你不加NOT NULL就意味着这条数据可以没有这个属性。设计建议业务上必须有的信息一律加NOT NULL并用DEFAULT提供兜底。这里有个实际经验先想想NULL和空字符串是两个概念。NULL表示“未知、不存在”空字符串表示“值为空白”。很多人把两者混用导致查询条件要同时判断IS NULL和非常痛苦。一个表里最好统一规则要么全部用NULL表示“无”要么全部用空串表示“无”我强烈推荐前者。CREATE TABLE products ( product_id INT IDENTITY PRIMARY KEY, product_name NVARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0 );一个小小的反常识加了NOT NULL的列在某些查询优化场景下反而对性能有利因为优化器知道该列不可能为NULL可以省掉一部分空值判断索引也能更高效地工作。2.5 默认值约束DEFAULT给字段一个不劳而获的兜底默认值约束是“如果插入时不提供值就用默认填充”。它保证了必填字段的完整性也减少了应用层的重复代码。常见用法创建时间默认GETDATE()或CURRENT_TIMESTAMP、状态字段默认0或active、计数器默认0、删除标记默认0。注意默认值约束不解决“主动传NULL”的问题——你显式插入NULL默认值不会生效所以通常情况下默认值列要配合NOT NULL一起使用。有个坑要提醒如果你后续修改了列属性、调整了默认值脚本老数据的默认值不会自动变更默认值只在INSERT时生效。这意味着修改默认约束前要考虑存量数据是否受影响。CREATE TABLE logs ( log_id INT IDENTITY PRIMARY KEY, log_level INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT GETDATE() );2.6 检查约束CHECK限死一个字段的取值范围检查约束用来限值字段的合法区间或匹配模式例如年龄必须在0到120之间、状态字段只能取枚举集合里的几个值。SQL Server、PostgreSQL都支持CHECK约束MySQL在8.0.16版本之前只解析不强制执行这点被很多人忽视。ALTER TABLE users ADD CONSTRAINT CK_users_age CHECK (age BETWEEN 0 AND 120);用CHECK约束可以把应用层的枚举校验下沉到数据库防止别的方式写入非法状态。但也不能过度使用跨越多个字段的复杂CHECK比如“结束时间必须大于开始时间”能用不过它会让表写入的约束检查变重高并发写入场景下要考虑这个损耗。3. 实操完整建表约束的组合拳怎么打3.1 字段级约束和表级约束约束可以声明在字段后面字段级约束也可以声明在表定义末尾表级约束。外键、复合主键、复合唯一键必须在表级声明因为涉及多个字段。字段级约束的优点是直观缺点是复合约束写不了。CREATE TABLE employees ( emp_id INT IDENTITY(1,1), emp_no VARCHAR(20) NOT NULL, name NVARCHAR(50) NOT NULL, phone VARCHAR(20), email VARCHAR(100), hire_date DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE), salary DECIMAL(10,2) CHECK (salary 0), department_id INT, status TINYINT NOT NULL DEFAULT 1, CONSTRAINT PK_employees PRIMARY KEY (emp_id), CONSTRAINT UQ_employees_empNo UNIQUE (emp_no), CONSTRAINT UQ_employees_phone UNIQUE (phone), CONSTRAINT FK_employees_dept FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT CK_employees_status CHECK (status IN (0, 1)) );这张表一口气把上一篇说到的约束都用上了。主键管唯一标识emp_no唯一约束管业务编号非空管必备字段默认值管状态和入职日期检查约束管薪资非负和状态枚举外键关联部门表。3.2 已存在的表怎么补约束很多项目的表是历史遗留的不能推倒重建。这时要用ALTER TABLE来补约束。注意加约束之前必须保证存量数据满足约束条件否则会直接报错。比如想在已有表上加CHECK约束但表里已经有年龄200的记录那么ALTER语句会失败。先清理再约束-- 步骤1找出脏数据 SELECT * FROM users WHERE age 0 OR age 120; -- 步骤2处理脏数据 UPDATE users SET age NULL WHERE age 0 OR age 120; -- 步骤3加约束 ALTER TABLE users ADD CONSTRAINT CK_users_age CHECK (age BETWEEN 0 AND 120);外键约束和唯一约束同理都要先确认存量数据没问题。这属于“先清理存量再锁住增量”的思路适用于所有约束后补场景。3.3 约束的删除与临时禁用有时候批量导入数据、做历史数据迁移希望先放数据再统一校验这时可以先临时禁用约束。SQL Server的做法是-- 禁用外键约束 ALTER TABLE orders NOCHECK CONSTRAINT FK_orders_user; -- 重新启用并校验存量数据 ALTER TABLE orders WITH CHECK CHECK CONSTRAINT FK_orders_user;MySQL则用SET foreign_key_checks 0来忽略外键检查。注意所有“禁用约束”的操作都要严格控制执行窗口千万别带着禁用的约束上线。删除约束的语法ALTER TABLE users DROP CONSTRAINT CK_users_age;主键约束的删除稍微特殊一点在有些数据库里还需要指定列ALTER TABLE users DROP CONSTRAINT PK_users;3.4 索引与约束的共生关系约束和索引是伴生关系。主键约束和唯一约束在几乎所有数据库里都会自动创建唯一索引外键约束通常也会触发创建普通索引。这意味着加唯一约束的同时唯一索引也建好了查询该字段速度很快建索引不一定要建约束但建唯一约束一定会建唯一索引删除唯一约束时对应的索引通常也会一并删掉。这个特性在设计大表的索引策略时很有用——如果你本来就想给某个字段加索引而且是唯一性场景直接用唯一约束一步到位索引也免了单独建。4. 约束实战中的高频坑位与排查思路4.1 主键选了可变的业务字段拿身份证、手机号、邮箱做物理主键当时只要SET IDENTITY_INSERT或者人工指定主键值都行等业务规则一改或者要求支持多手机号、多邮箱的时候关联表全炸。改成代理主键自增ID或GUID也是大动干戈。主键一定是永不变化的字段最好是无业务含义的占位字段。4.2 FOREIGN KEY被卡父表删不动你可能遇到过这种情况父表里有数据被子表引用DELETE父表记录直接被拒。这不是数据库坏了是外键约束在保护完整性。如果你确实要删父表记录先想清楚子表数据怎么办联删、置空、还是换个状态标记。我建议做个“约束冻结”方案不要动父表记录加一个is_deleted标记配合过滤视图。4.3 检查约束在MySQL里不生效MySQL 8.0.16之前的版本CHECK约束会被解析但忽略。很多从SQL Server迁移过来的开发者踩这个坑——建表语句里写了CHECK插入非法数据也不报错还以为MySQL有毛病。解决办法升级到8.0.16以上或者改用触发器兜底。如果你的MySQL版本比较老可以先show create table看看CHECK有没有被真实执行。4.4 唯一约束遇上NULL的“同值不同命”SQL标准里NULL不等于NULL所以唯一约束下的多个NULL可以共存。但这个行为在不同数据库里不一致MySQL的InnoDB允许多个NULLOracle默认也不认为两个NULL冲突SQL Server也允许多个NULL默认非过滤唯一索引。如果你想让“手机号为空的记录也不能超过一条”普通唯一约束做不到要么用空串替代NULL要么加过滤索引SQL Server的“WHERE phone IS NOT NULL”唯一索引或PostgreSQL的partial index。4.5 约束命名的可读性灾难很多建表脚本不写约束名数据库自动生成的约束名是一串随机字符比如DF__users__status__1F98B2C1。几个月后你要改约束一看这名字完全不知道它是干嘛的。规范做法是统一命名规则约束类型推荐前缀示例主键PK_PK_users外键FK_FK_orders_user唯一UQ_UQ_users_email检查CK_CK_users_age默认值DF_DF_users_status别嫌麻烦这串字符未来会帮你省下无数翻看脚本的时间。5. 约束设计的优先级参考约束不是越多越好也不是越少越好。我总结了一个大概的判断顺序主键必须有任何表都不能裸奔外键在OLTP系统尽量加数据一致性永远优先于那一点性能损耗业务唯一字段一定加唯一约束宁可在测试环境被重复数据恶心也不要在生产环境被脏数据恶心必填字段务必NOT NULL配合DEFAULT兜底CHECK约束用在枚举值、取值范围有限的场景复杂跨字段校验能用但别滥用默认值约束能简化应用层赋值逻辑但要配合NOT NULL理解它的生效边界。另外插入一个容易被忽略的统计约束约束不是静态的DLL它和数据一起成长。业务规则变了约束也要跟着变。我给团队定的规矩是每次改表结构必须走一个检查清单含主键策略、外键影响、唯一性变化、默认值更新、检查约束重审。6. 最后一个实战案例从零织一张订单约束网前面单独讲约束太多最后用一个综合案例串一遍。假设要建一个电商的订单系统有四张表用户表、商品表、订单表、订单明细表。CREATE TABLE users ( user_id INT IDENTITY(1,1), user_name NVARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, email VARCHAR(100), register_time DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT PK_users PRIMARY KEY (user_id), CONSTRAINT UQ_users_phone UNIQUE (phone), CONSTRAINT UQ_users_email UNIQUE (email) ); CREATE TABLE products ( product_id INT IDENTITY(1,1), product_name NVARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, CONSTRAINT PK_products PRIMARY KEY (product_id), CONSTRAINT CK_products_price CHECK (price 0), CONSTRAINT CK_products_stock CHECK (stock 0) ); CREATE TABLE orders ( order_id INT IDENTITY(1,1), order_no VARCHAR(32) NOT NULL, user_id INT NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT PK_orders PRIMARY KEY (order_id), CONSTRAINT UQ_orders_orderNo UNIQUE (order_no), CONSTRAINT FK_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id), CONSTRAINT CK_orders_status CHECK (status IN (0, 1, 2, 3)), CONSTRAINT CK_orders_amount CHECK (total_amount 0) ); CREATE TABLE order_items ( item_id INT IDENTITY(1,1), order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, CONSTRAINT PK_order_items PRIMARY KEY (item_id), CONSTRAINT FK_order_items_order FOREIGN KEY (order_id) REFERENCES orders(order_id), CONSTRAINT FK_order_items_product FOREIGN KEY (product_id) REFERENCES products(product_id), CONSTRAINT CK_order_items_qty CHECK (quantity 0) );这个设计里每个约束都有明确职责订单号唯一防止重复下单用户外键保证订单归属有效用户状态检查约束限定订单流转区间商品库存非负阻止卖超为负。真正上了生产之后你会发现数据库层面拦截的非法数据比应用层想象的要多得多。最后说句实在话。约束这东西平时不显山不露水建表的时候多花五分钟想清楚能帮你后面少加一个星期的班。回头我可能还会写一篇关于索引的笔记到时候你就知道约束和索引经常要一起设计建表的时候多花这几分钟是非常划算的。