尧图网页设计 
1. 约束的本质数据库的数据质量守门员远比应用层校验可靠1.1 从完整性的三个层面来理解约束约束在SQL里是个存在感很低、但上线后最能保命的东西。很多人在学习阶段接触过PRIMARY KEY、NOT NULL这些关键字但等真正开始写业务表时往往要么漏掉要么只加了主键就草草收工。我先讲一个自己碰过的案例之前排查一个线上商城的数据错乱用户表里同一个手机号注册了十几条记录到最后系统里根本分不清哪条是“最新用户”。根因特别简单——建表时没有给手机号字段加唯一约束。数据一旦以脏数据的形式进库清洗的成本比建表时补一行约束的成本高几十倍。从那以后我每建一张表都会把约束当成第一件事来设计而不是最后的补丁。关系数据库里的约束本质是数据完整性Data Integrity的落地手段。完整性可以拆成三个层面这也是一张表设计时该依次检查的三个问题完整性类型包含的约束解决的问题域完整性列级NOT NULL、DEFAULT、CHECK这一列是否允许为空、不填时给什么默认值、取值范围和格式怎么限制实体完整性行级PRIMARY KEY、UNIQUE表格里的每一行能不能被唯一区分会不会出现“长得一模一样”的记录引用完整性表级FOREIGN KEY子表里引用的父表数据是否真实存在关联关系会不会断掉这套分类不是DBA背概念用的而是设计表时可以直接照顺序自检的清单。我面对一张新表通常会连续问自己几个问题这一列语义上允许为空吗如果没人传值数据库应该自动填什么值的范围、格式怎么限制这张表的唯一身份靠哪一列它引用了别的表吗如果对方删了这条记录该怎么办每张表都过一遍这五个问题约束基本不会漏。生活化类比的话约束就像住酒店时的门禁系统。NOT NULL是非空门禁没有房卡不能进电梯DEFAULT是自动派位没指定房间时给你安排到默认楼层CHECK是楼层限制超过合法层数直接报警FOREIGN KEY是房卡联动你的房卡在另一个楼层的房态系统里必须真实存在PRIMARY KEY则是房卡号全局唯一。这样想约束就不再是枯燥的语法了它解决的全是真实业务里的“意外”。1.2 为什么数据库约束比应用层校验更可靠很多人会问一个问题业务代码里已经写了各种参数校验为什么还要在数据库层再加一层最现实的原因是不是只有业务接口在写数据。后台管理员的批量脚本、报表工具直连数据库、老系统订正数据的SQL、甚至某个同事手滑在Navicat里敲了一条INSERT这些写入路径根本不会经过应用层的校验逻辑。我曾见过一个快速修复线上问题的方案就是直接连生产库UPDATE了一列如果那一列有CHECK约束随手改成一个不允许的状态当场就会报错而不是带病运行。应用层校验负责给用户友好提示数据库约束负责强制兜底两者是互补关系不是二选一。另外约束还能在数据迁移和同步阶段暴露问题。假设你从老库往新库导数据目标表上有约束不合规的数据导入时就会直接失败把问题暴露在导入阶段没有约束的话脏数据会在新库里继续潜伏可能几个月后才在业务上爆发。我是在做一次老系统迁移时意识到这一点的——源库表结构几乎没有约束导出的数据里什么妖魔鬼怪都有幸好目标库补上了约束第一轮导入就筛出了一大批积压多年的坏数据。提示约束既不是越多越好也不是越少越好。缺约束的后果是脏数据但过量约束尤其是不合理的CHECK会影响写入速度并增加运维成本。后面几章会逐个讲清楚这个度该怎样把握。2. 主键与唯一约束最基础也最容易留下脏数据隐患2.1 主键的选择自增、GUID还是业务字段主键是实体完整性的核心。不建主键的表也能建成功但没有主键就意味着行与行无法可靠区分后面做增量同步、按主键去重、表关联、数据订正都会很受罪所以主键几乎是每张表都必须有的。主键列怎么选不同方案差异很大自增整数MySQL里的AUTO_INCREMENT、SQL Server里的IDENTITY(1,1)。优点是写入快、索引紧凑、性能好。缺点是分布式场景下跨库合并容易冲突而且ID会暴露业务量比如一家新公司订单量很少对外接口如果直接透出自增ID竞争对手可以通过ID增长趋势推测运营数据。UUID/GUID全局唯一分布式友好对外不可猜测。缺点是字符串类型占用空间大而且无序的UUID做主键会造成索引页频繁分裂写入性能会比自增ID差不少。SQL Server提供了NEWSEQUENTIALID()生成有序GUIDMySQL 8.0.13之后也可以借助合适的UUID版本缓解随机性问题但老库上直接用UUID()还是要警惕页分裂。业务主键比如用身份证号做用户表主键听起来是“天然唯一”的但身份证号会变、长度超标、表面还带着隐私信息。业务字段做主键的最大隐患是“业务规则一变主键就要动”而几乎没有比业务字段更易变的东西了。我个人的原则是主键用无意义的代理键要么自增整数要么GUID业务字段的唯一性需要保证时不要去抢主键的位置而是用唯一约束另外声明。主键负责存储层的身份标识唯一约束负责业务层的“不重复”这两个职责分开之后维护成本会低很多。2.2 主键和唯一约束的几个本质区别很多初学者分不清主键和唯一约束以为它们都是“不重复”但其实差别很大。我把关键差异列出来建表时对照着选就行对比项主键 PRIMARY KEY唯一约束 UNIQUE数量每张表最多只能有一个一张表可以有多个NULL绝对不允许多数数据库允许多个NULLSQL Server特殊见2.3自动索引通常生成主键索引InnoDB中就是聚簇索引建立一个唯一索引被外键引用可以可以业务含义存储身份标识换一个业务规则也不该变保证某个业务字段不重复比如邮箱、手机号、订单号这里有个值得深挖的点外键引用的目标列为什么必须是主键或唯一约束因为外键的语义是“子表引用父表中唯一存在的那一行”如果被引用列允许重复子表就不知道该对应哪一行了。所以外键不能指向一个普普通通的索引列这个限制是引用完整性的基础。2.3 唯一约束与NULL的“温柔陷阱”这是最容易踩坑、又很少有人讲清楚的一节。UNIQUE约束只保证“不为NULL的值不重复”而NULL代表未知数据库通常不会把NULL当作参与唯一性比较的值。但不同数据库对NULL的处理规则并不完全一致MySQL、PostgreSQL、Oracle唯一索引列上可以存在多个NULL它们之间互不冲突。SQL Server默认情况下唯一约束和唯一索引会把NULL也当做一个值来处理也就是说整列只允许出现一个NULL。如果你想实现“允许有多条记录缺这个值但是有值时不重复”就需要用过滤唯一索引绕过去写法是CREATE UNIQUE INDEX UQ_Users_Phone ON Users(Phone) WHERE Phone IS NOT NULL;这个问题不是书本上的冷知识是真会咬人的。曾经有团队把表从MySQL迁到SQL Server原来允许多个NULL的手机号列迁移后唯一索引直接报“重复键”数据同步全部卡住。所以只要表里可能有空值SQL Server默认的唯一约束就要格外小心。一张表的NULL策略最好在建表时就定下来而不是等迁移或插入数据时再被数据库教育。2.4 Navicat里给字段加唯一约束的具体操作如果不想手敲SQL在Navicat里加唯一约束也非常顺手我常用的是索引面板的方式打开表设计器选中需要加唯一约束的表。切换到“索引”页签不是字段页签。点击“”新增一条索引。输入索引名建议按“UK_表名_字段名”的规范来比如uk_users_email。字段列选择email索引类型选择UNIQUE。保存变更。这里有个小提示在字段编辑界面里也有一个“唯一”勾选框勾选后也能建唯一约束但那个方式对约束名的控制比较少团队协作时很容易产生一堆含义不明的约束名。既然要加约束不如一开始就用索引面板把命名规范做起来。3. 外键约束从“删不掉的数据”到级联动作的完整决策链3.1 引用完整性四种外键动作的取舍外键约束的完整语法大概是这样的FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE RESTRICT真正需要决策的关键点在ON DELETE和ON UPDATE后面的动作常用有四种CASCADE级联父表记录删除/更新时子表引用它的记录也跟着删除/更新。SET NULL父表记录删除时子表的外键列被置为NULL前提是子表该列允许为空。RESTRICT / NO ACTION如果子表还有记录引用父表直接禁止父表删除/更新。不同数据库的实现略有差异MySQL里两者行为基本一致。SET DEFAULT标准SQL里有定义但InnoDB实际基本不生效别指望它这也是一个容易误用的点。怎么选主要看子记录和父记录之间的关系性质。如果父子关系是“从属关系”子记录没有独立存在价值比如“订单”和“订单明细”删除订单时明细一起删掉是合理的可以用CASCADE。如果父记录删除后历史子记录必须保留比如“商户”和“交易流水”就坚决不要用CASCADE用RESTRICT卡住删除或者更推荐的做法是干脆不物理删除给父表加个is_deleted标记做逻辑删除。如果父记录删了子记录还想保留但变成“无主数据”可以用SET NULL比如员工离职后他名下曾经处理的工单还在把处理人字段置空比直接删除工单更符合业务事实。3.2 一个真实的删除失败现场五步定位外键最容易让新手崩溃的时刻就是一条明明很普通的DELETE语句突然报错。我拆一个现场讲一下定位思路。场景执行DELETE FROM categories WHERE id 5;数据库返回“cannot delete or update a parent row: a foreign key constraint fails”之类的错误。第一步别慌把完整错误信息复制下来。错误信息里通常已经带出了子表名和约束名比如orders表的fk_orders_category这等于直接告诉了你问题在哪。第二步如果错误信息不够清楚可以通过系统表查外键关系SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME categories;第三步看到orders表引用了categories后再查一查到底有哪些订单引用了这条分类SELECT id, category_id FROM orders WHERE category_id 5 LIMIT 20;第四步判断这些订单属于什么性质。如果业务允许先把子表数据处理掉比如把订单转移分类、软删除或者彻底清理如果业务不允许那就说明这条分类不该删正确的做法是把它标记成“停用”而不是物理删除。第五步等所有引用该父表数据的子表都处理干净再回头删父表记录。这套流程看起来多跑了几步但比“直接把外键DROP掉再强删父表”安全太多。有些人遇到删不掉就先把外键约束删了强制删父表短期内问题消失但子表里立刻出现悬空引用后面连JOIN查询都会查出一堆脏数据。注意删除父表数据时提示外键约束失败是在保护你的数据不是在跟你作对。先处理子表再处理父表这个顺序就是规则本身。3.3 生产环境到底该不该用外键这个话题在团队里经常吵出两个阵营。禁用派觉得外键影响写入性能、锁范围大、分库分表之后根本没法用所以很多互联网团队规范里直接写“数据库层禁用外键一致性交给应用层”。拥护派则认为中小项目单体库的外键带来的完整性保障远大于性能开销而且大多数性能问题出在外键列缺少索引而不是外键本身。我的经验是两边都有道理关键看场景。微服务加分库分表的架构下外键基本是失效的数据库层面的引用完整性跨不了多个物理库一致性必须靠应用层事务、消息对账、事件最终一致性这些方案解决这时候坚持用外键没意义。但如果是单体后台管理系统、订单交易系统、内容管理这类一个库能放下的项目外键该加还是要加尤其是一对多关系中“多”的这一端外键能拦住一大类应用层遗漏的bug。不过外键也有两个很实际的坑。第一个是建表顺序必须先建父表再建子表否则会直接报“无法添加外键约束”。第二个是导入数据如果往一张带外键的表中导入大批历史数据可以先关闭外键检查导入完再打开但打开后一定要做一次完整性校验排查有没有孤儿数据。MySQL的写法是SET FOREIGN_KEY_CHECKS 0; -- 批量导入数据 SET FOREIGN_KEY_CHECKS 1;4. 检查约束与默认值低调但关键时刻能兜底的两员大将4.1 CHECK约束“假生效”的版本坑CHECK约束用来限制单列的取值范围或格式比如枚举状态、数值范围、简单字符串格式。但这里藏着一个很多人踩过的大坑MySQL 5.7及更早版本支持写CHECK语法解析却不会执行你写了CHECK (status IN (0,1))插入status 9照样能成功。到了MySQL 8.0.16CHECK约束才真正开始强制生效。MariaDB则稍有不同从10.2.1开始正式执行。这个“版本差”真的很要命。如果你在MySQL 5.7上建了一张表以为CHECK能拦住非法数据其实它除了占个位置什么都没干。老项目从5.7升级到8.0时也要提前知道之前被“宽容”放过的不合法数据不会因为升级自动变干净但升级后新增写入会被严格拦截业务方需要做好对接准备。CHECK约束适合放的场景很明确枚举状态CHECK (status IN (pending, paid, shipped, cancelled))数值范围CHECK (price 0)、CHECK (age BETWEEN 0 AND 120)简单格式CHECK (email LIKE %%)记住CHECK只负责写入时校验这一行的列值是否符合表达式它管不了跨行、跨表的逻辑。像“某个订单的累计金额不能超过用户余额”这种跨表依赖不应该指望CHECK那是应用层事务或触发器该干的事。4.2 DEFAULT默认值时间戳和GUID的两种经典用法默认值负责一件事这条记录创建时如果某个字段没有被传值数据库会自动帮你填上什么。它是最容易被低估的约束之一也是最容易改善数据质量的约束之一。时间戳是最常见的默认值用法MySQLcreate_time DATETIME DEFAULT CURRENT_TIMESTAMP如果需要更新时自动刷新可以写成update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。注意ON UPDATE是MySQL的语法SQL Server没有这种自动更新能力需要触发器配合。SQL Servercreate_time DATETIME DEFAULT GETDATE()。另一个典型用法是给“对外暴露的标识字段”设GUID默认值这也是很多业务里真正需要的SQL Serveruser_guid UNIQUEIDENTIFIER DEFAULT NEWID()或者用NEWSEQUENTIALID()生成更有序的值。MySQL 8.0.13及以上user_guid CHAR(36) DEFAULT (UUID())。PostgreSQL先启用扩展CREATE EXTENSION IF NOT EXISTS pgcrypto;再写user_guid UUID DEFAULT gen_random_uuid()。为什么推荐把“对外GUID”和“内部主键”分开设计因为内部主键用自增整数性能最好而对外GUID可以让接口、订阅系统、防遍历使用即使ID被猜到也不会泄露业务量。两个字段各有各的职责。4.3 “默认值拦不住NULL”这个细节默认值有一个非常容易误解的细节它只在“这一列完全没有出现在INSERT语句里”时生效。如果INSERT语句显式写成了NULL默认值并不会把NULL替换掉除非你用的数据库或写法有特殊处理。所以只要业务上这一列是必须有值的正确姿势一定是“默认值非空约束”组合着写status TINYINT NOT NULL DEFAULT 0 CHECK (status IN (0,1)),这样既能自动填默认值又能拒绝显式传入的NULL。我见过不少表只加了DEFAULT没加NOT NULL然后应用层传了个NULL进来结果NULL照单全收默认值完全没起作用。这个组合是设计新表时最常用的一招写建表语句时可以当成肌肉记忆。5. 约束运行期的三个隐藏雷区索引、命名与修改成本5.1 外键与索引的关系不同数据库表现完全不同先说结论MySQL的InnoDB在创建外键时如果外键列上还没有合适的索引会自动创建一个索引但SQL Server不会自动创建需要你手动在子表的外键列上建索引。很多开发者习惯性以为“外键自带索引”这个结论在不同数据库上会带来完全不同的后果跨库迁移时尤其要注意。不管数据库是否自动创建索引实际开发里我都建议在外键列上显式建立合适索引。这不仅仅是让外键检查更快更是为了让按父表ID查子表的业务查询不要全表扫描。我接手过一个订单查询接口慢得离谱看执行计划发现子表的外键列完全没走索引父表ID一进来就是全表扫加完索引之后查询时间从几秒降到几十毫秒。这个优化跟约束没什么关系纯粹是索引没建对。说到慢SQL优化我要多提醒一句遇到外键关联查询慢先用执行计划确认有没有走索引而不是第一时间怀疑是外键拖慢了性能。外键本身不是慢SQL的元凶那个字段上没有索引才是。5.2 不命名约束的代价建约束时如果不主动起名数据库会自动生成一堆像CONSTRAINT_1、orders_ibfk_1、DF__users__create__3D6E0B2D这种名字。初期没有任何感觉但等你想删某个约束时就得先花半天在各种系统视图里找它到底叫什么。SQL Server给自动生成的默认值约束名尤其恶心中间会夹一串十六进制哈希后缀完全不可读。推荐的做法是建立一套团队级别的命名规范一看到名字就知道约束类型和归属主键PK_表名_字段名如PK_users_id唯一UK_表名_字段名如UK_users_email外键FK_表名_父表名_字段名如FK_orders_users_user_id检查CK_表名_字段名如CK_orders_price默认DF_表名_字段名如DF_users_create_time只要全组都按这个规范走后面任何人查约束时都清清楚楚不需要猜。5.3 修改和删除约束的标准姿势SQL里没有ALTER CONSTRAINT这种一步到位改约束的语法。想调整一个约束必须“先删后加”。拿MySQL为例修改一个外键的动作是ALTER TABLE orders DROP FOREIGN KEY fk_orders_user_id; ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;SQL Server修改默认值约束也是同一个套路ALTER TABLE users DROP CONSTRAINT DF_users_create_time; ALTER TABLE users ADD CONSTRAINT DF_users_create_time DEFAULT GETDATE() FOR create_time;还有一个很常见的卡壳点如果主键被外键引用了直接DROP PRIMARY KEY会失败必须先删除引用它的外键再处理主键。修改主键更是如此依赖关系不处理干净DDL根本执行不下去。想确认当前的约束名两个常用入口记一下MySQL可以用SHOW CREATE TABLE 表名;SQL Server可以查sys.default_constraints和sys.foreign_keys。5.4 约束会不会拖慢写入和查询合理使用约束对查询的影响其实很小查询引擎甚至还能利用约束推导优化执行计划。真正影响写入性能的点在于外键检查需要去子表或父表验证记录批量DML时如果外键列没有索引性能会明显下降同时外键检查还会带来额外的锁竞争。针对这个点我的优化思路很直接用执行计划定位而不是凭感觉猜瓶颈到底在哪索引设计优先覆盖“外键列WHERE高频条件列”大批量导入时MySQL可以临时关掉外键检查导入完再打开并做完整性校验不要因为“觉得慢”就顺手把外键删掉先验证是不是外键检查真的成了瓶颈很多时候建完索引问题就没了。6. 一张包含全部约束的订单表从零开始怎么设计6.1 完整的用户表与订单表建表语句前面几章把约束拆开讲了这一章串起来看一个完整案例。以最常见的电商场景为例设计一张用户表和一张订单表把主键、唯一约束、外键、检查约束、默认值、非空约束全部放进去。用户表CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 内部主键自增, user_guid CHAR(36) NOT NULL COMMENT 对外GUID防止ID遍历, username VARCHAR(50) NOT NULL COMMENT 登录名业务唯一, email VARCHAR(128) NULL COMMENT 邮箱允许为空, phone VARCHAR(20) NULL COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常, 0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_users_user_guid (user_guid), UNIQUE KEY uk_users_username (username), UNIQUE KEY uk_users_email (email), UNIQUE KEY uk_users_phone (phone), CONSTRAINT ck_users_status CHECK (status IN (0,1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;订单表CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单主键, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号全局唯一, user_id BIGINT UNSIGNED NOT NULL COMMENT 下单用户引用users.id, total_amount DECIMAL(10,2) NOT NULL COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, remark VARCHAR(255) NULL COMMENT 备注, expect_arrive_date DATE NULL COMMENT 预计送达日期未确定为NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_orders_order_no (order_no), KEY idx_orders_user_id (user_id), CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT ck_orders_status CHECK (status IN (0,1,2,3,4)), CONSTRAINT ck_orders_total_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;6.2 逐条拆解这样设计的原因先看用户表。主键选了自增id没有用username或phone做主键因为用户名字可以改、手机号可以换只有内部自增ID是稳定不变的。user_guid是给外部接口用的GUID有一层唯一约束这对应前面说过的“对内用整数主键、对外用GUID防遍历”的思路。username、email、phone三条唯一约束都是为了拦住“注册时重复数据”的业务问题。注意email和phone允许NULL这个用例落在MySQL语义下允许多个NULL但如果这套表将来要迁到SQL Server这两个唯一索引会触发“只能一个NULL”的差异迁移前必须评估。订单表里order_no是业务订单号它带了唯一约束但没有当主键。因为订单号在分布式场景下可能包含分库分表位、日期、序号生成规则复杂主键依然用自增ID保持简洁。user_id上建了一个普通索引idx_orders_user_id不是唯一索引——因为一个用户会有多笔订单。这个普通索引同时解决了两个问题按用户查订单走索引外键检查也有索引可用。status字段用一个CHECK约束限定在五个合法状态内。这里我要再强调一下版本问题这套DDL必须在MySQL 8.0.16及以上版本才能真正拦住非法状态如果库里还是5.7这个CHECK形同虚设应用层校验就必须承担全部责任。total_amount的CHECK设为大于等于0避免负金额入库。expect_arrive_date允许NULLNULL表示“还没确定送达时间”。有同学可能会问为什么不加一个“日期不能早于今天”的CHECK我的建议是这类依赖当前时间的校验更适合放应用层。因为不同数据库对CHECK里调用当前日期函数的支持度差异很大很容易产生版本坑数据库约束没必要在这种场景拼极限。6.3 老表补约束的先后顺序建议如果你手上是一张已经运行了很久的老表字段里全是历史数据想补约束不能一下全上否则一条ALTER TABLE可能直接因为存量脏数据执行失败。我的建议是按优先级分步来先补NOT NULL。把那些“逻辑上必须有值”的字段先补上这是改动最小、收益最大的一个。但如果空值已经存在需要先处理存量数据要么补默认值要么清理掉。再补唯一约束。像是手机号、邮箱、订单号这种业务唯一字段补唯一约束前必须跑一遍查重SQL把重复记录合并或清洗掉否则约束根本加不上。然后补外键。补外键前要确认两张表的数据能对得上子表里不能有引用不到父表的孤儿数据。最后再考虑CHECK约束。因为CHECK的版本坑最多先确认数据库版本支持真实执行再评估存量数据是否都满足新加的校验条件。如果存量数据有不满足的可以考虑分两步先修数据再上约束。设计约束这件事我的体感是建表时多花十分钟后面能省上百小时的查数据时间。如果你实在不知道从哪下手就先把NOT NULL和唯一约束补上这两类约束对数据质量的改善最直接、风险也最低。每补一个约束本质上都是在给未来的自己少埋一个坑。 数据库约束详解：主键、外键、唯一约束与数据完整性设计
1. 约束的本质数据库的数据质量守门员远比应用层校验可靠1.1 从完整性的三个层面来理解约束约束在SQL里是个存在感很低、但上线后最能保命的东西。很多人在学习阶段接触过PRIMARY KEY、NOT NULL这些关键字但等真正开始写业务表时往往要么漏掉要么只加了主键就草草收工。我先讲一个自己碰过的案例之前排查一个线上商城的数据错乱用户表里同一个手机号注册了十几条记录到最后系统里根本分不清哪条是“最新用户”。根因特别简单——建表时没有给手机号字段加唯一约束。数据一旦以脏数据的形式进库清洗的成本比建表时补一行约束的成本高几十倍。从那以后我每建一张表都会把约束当成第一件事来设计而不是最后的补丁。关系数据库里的约束本质是数据完整性Data Integrity的落地手段。完整性可以拆成三个层面这也是一张表设计时该依次检查的三个问题完整性类型包含的约束解决的问题域完整性列级NOT NULL、DEFAULT、CHECK这一列是否允许为空、不填时给什么默认值、取值范围和格式怎么限制实体完整性行级PRIMARY KEY、UNIQUE表格里的每一行能不能被唯一区分会不会出现“长得一模一样”的记录引用完整性表级FOREIGN KEY子表里引用的父表数据是否真实存在关联关系会不会断掉这套分类不是DBA背概念用的而是设计表时可以直接照顺序自检的清单。我面对一张新表通常会连续问自己几个问题这一列语义上允许为空吗如果没人传值数据库应该自动填什么值的范围、格式怎么限制这张表的唯一身份靠哪一列它引用了别的表吗如果对方删了这条记录该怎么办每张表都过一遍这五个问题约束基本不会漏。生活化类比的话约束就像住酒店时的门禁系统。NOT NULL是非空门禁没有房卡不能进电梯DEFAULT是自动派位没指定房间时给你安排到默认楼层CHECK是楼层限制超过合法层数直接报警FOREIGN KEY是房卡联动你的房卡在另一个楼层的房态系统里必须真实存在PRIMARY KEY则是房卡号全局唯一。这样想约束就不再是枯燥的语法了它解决的全是真实业务里的“意外”。1.2 为什么数据库约束比应用层校验更可靠很多人会问一个问题业务代码里已经写了各种参数校验为什么还要在数据库层再加一层最现实的原因是不是只有业务接口在写数据。后台管理员的批量脚本、报表工具直连数据库、老系统订正数据的SQL、甚至某个同事手滑在Navicat里敲了一条INSERT这些写入路径根本不会经过应用层的校验逻辑。我曾见过一个快速修复线上问题的方案就是直接连生产库UPDATE了一列如果那一列有CHECK约束随手改成一个不允许的状态当场就会报错而不是带病运行。应用层校验负责给用户友好提示数据库约束负责强制兜底两者是互补关系不是二选一。另外约束还能在数据迁移和同步阶段暴露问题。假设你从老库往新库导数据目标表上有约束不合规的数据导入时就会直接失败把问题暴露在导入阶段没有约束的话脏数据会在新库里继续潜伏可能几个月后才在业务上爆发。我是在做一次老系统迁移时意识到这一点的——源库表结构几乎没有约束导出的数据里什么妖魔鬼怪都有幸好目标库补上了约束第一轮导入就筛出了一大批积压多年的坏数据。提示约束既不是越多越好也不是越少越好。缺约束的后果是脏数据但过量约束尤其是不合理的CHECK会影响写入速度并增加运维成本。后面几章会逐个讲清楚这个度该怎样把握。2. 主键与唯一约束最基础也最容易留下脏数据隐患2.1 主键的选择自增、GUID还是业务字段主键是实体完整性的核心。不建主键的表也能建成功但没有主键就意味着行与行无法可靠区分后面做增量同步、按主键去重、表关联、数据订正都会很受罪所以主键几乎是每张表都必须有的。主键列怎么选不同方案差异很大自增整数MySQL里的AUTO_INCREMENT、SQL Server里的IDENTITY(1,1)。优点是写入快、索引紧凑、性能好。缺点是分布式场景下跨库合并容易冲突而且ID会暴露业务量比如一家新公司订单量很少对外接口如果直接透出自增ID竞争对手可以通过ID增长趋势推测运营数据。UUID/GUID全局唯一分布式友好对外不可猜测。缺点是字符串类型占用空间大而且无序的UUID做主键会造成索引页频繁分裂写入性能会比自增ID差不少。SQL Server提供了NEWSEQUENTIALID()生成有序GUIDMySQL 8.0.13之后也可以借助合适的UUID版本缓解随机性问题但老库上直接用UUID()还是要警惕页分裂。业务主键比如用身份证号做用户表主键听起来是“天然唯一”的但身份证号会变、长度超标、表面还带着隐私信息。业务字段做主键的最大隐患是“业务规则一变主键就要动”而几乎没有比业务字段更易变的东西了。我个人的原则是主键用无意义的代理键要么自增整数要么GUID业务字段的唯一性需要保证时不要去抢主键的位置而是用唯一约束另外声明。主键负责存储层的身份标识唯一约束负责业务层的“不重复”这两个职责分开之后维护成本会低很多。2.2 主键和唯一约束的几个本质区别很多初学者分不清主键和唯一约束以为它们都是“不重复”但其实差别很大。我把关键差异列出来建表时对照着选就行对比项主键 PRIMARY KEY唯一约束 UNIQUE数量每张表最多只能有一个一张表可以有多个NULL绝对不允许多数数据库允许多个NULLSQL Server特殊见2.3自动索引通常生成主键索引InnoDB中就是聚簇索引建立一个唯一索引被外键引用可以可以业务含义存储身份标识换一个业务规则也不该变保证某个业务字段不重复比如邮箱、手机号、订单号这里有个值得深挖的点外键引用的目标列为什么必须是主键或唯一约束因为外键的语义是“子表引用父表中唯一存在的那一行”如果被引用列允许重复子表就不知道该对应哪一行了。所以外键不能指向一个普普通通的索引列这个限制是引用完整性的基础。2.3 唯一约束与NULL的“温柔陷阱”这是最容易踩坑、又很少有人讲清楚的一节。UNIQUE约束只保证“不为NULL的值不重复”而NULL代表未知数据库通常不会把NULL当作参与唯一性比较的值。但不同数据库对NULL的处理规则并不完全一致MySQL、PostgreSQL、Oracle唯一索引列上可以存在多个NULL它们之间互不冲突。SQL Server默认情况下唯一约束和唯一索引会把NULL也当做一个值来处理也就是说整列只允许出现一个NULL。如果你想实现“允许有多条记录缺这个值但是有值时不重复”就需要用过滤唯一索引绕过去写法是CREATE UNIQUE INDEX UQ_Users_Phone ON Users(Phone) WHERE Phone IS NOT NULL;这个问题不是书本上的冷知识是真会咬人的。曾经有团队把表从MySQL迁到SQL Server原来允许多个NULL的手机号列迁移后唯一索引直接报“重复键”数据同步全部卡住。所以只要表里可能有空值SQL Server默认的唯一约束就要格外小心。一张表的NULL策略最好在建表时就定下来而不是等迁移或插入数据时再被数据库教育。2.4 Navicat里给字段加唯一约束的具体操作如果不想手敲SQL在Navicat里加唯一约束也非常顺手我常用的是索引面板的方式打开表设计器选中需要加唯一约束的表。切换到“索引”页签不是字段页签。点击“”新增一条索引。输入索引名建议按“UK_表名_字段名”的规范来比如uk_users_email。字段列选择email索引类型选择UNIQUE。保存变更。这里有个小提示在字段编辑界面里也有一个“唯一”勾选框勾选后也能建唯一约束但那个方式对约束名的控制比较少团队协作时很容易产生一堆含义不明的约束名。既然要加约束不如一开始就用索引面板把命名规范做起来。3. 外键约束从“删不掉的数据”到级联动作的完整决策链3.1 引用完整性四种外键动作的取舍外键约束的完整语法大概是这样的FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE RESTRICT真正需要决策的关键点在ON DELETE和ON UPDATE后面的动作常用有四种CASCADE级联父表记录删除/更新时子表引用它的记录也跟着删除/更新。SET NULL父表记录删除时子表的外键列被置为NULL前提是子表该列允许为空。RESTRICT / NO ACTION如果子表还有记录引用父表直接禁止父表删除/更新。不同数据库的实现略有差异MySQL里两者行为基本一致。SET DEFAULT标准SQL里有定义但InnoDB实际基本不生效别指望它这也是一个容易误用的点。怎么选主要看子记录和父记录之间的关系性质。如果父子关系是“从属关系”子记录没有独立存在价值比如“订单”和“订单明细”删除订单时明细一起删掉是合理的可以用CASCADE。如果父记录删除后历史子记录必须保留比如“商户”和“交易流水”就坚决不要用CASCADE用RESTRICT卡住删除或者更推荐的做法是干脆不物理删除给父表加个is_deleted标记做逻辑删除。如果父记录删了子记录还想保留但变成“无主数据”可以用SET NULL比如员工离职后他名下曾经处理的工单还在把处理人字段置空比直接删除工单更符合业务事实。3.2 一个真实的删除失败现场五步定位外键最容易让新手崩溃的时刻就是一条明明很普通的DELETE语句突然报错。我拆一个现场讲一下定位思路。场景执行DELETE FROM categories WHERE id 5;数据库返回“cannot delete or update a parent row: a foreign key constraint fails”之类的错误。第一步别慌把完整错误信息复制下来。错误信息里通常已经带出了子表名和约束名比如orders表的fk_orders_category这等于直接告诉了你问题在哪。第二步如果错误信息不够清楚可以通过系统表查外键关系SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME categories;第三步看到orders表引用了categories后再查一查到底有哪些订单引用了这条分类SELECT id, category_id FROM orders WHERE category_id 5 LIMIT 20;第四步判断这些订单属于什么性质。如果业务允许先把子表数据处理掉比如把订单转移分类、软删除或者彻底清理如果业务不允许那就说明这条分类不该删正确的做法是把它标记成“停用”而不是物理删除。第五步等所有引用该父表数据的子表都处理干净再回头删父表记录。这套流程看起来多跑了几步但比“直接把外键DROP掉再强删父表”安全太多。有些人遇到删不掉就先把外键约束删了强制删父表短期内问题消失但子表里立刻出现悬空引用后面连JOIN查询都会查出一堆脏数据。注意删除父表数据时提示外键约束失败是在保护你的数据不是在跟你作对。先处理子表再处理父表这个顺序就是规则本身。3.3 生产环境到底该不该用外键这个话题在团队里经常吵出两个阵营。禁用派觉得外键影响写入性能、锁范围大、分库分表之后根本没法用所以很多互联网团队规范里直接写“数据库层禁用外键一致性交给应用层”。拥护派则认为中小项目单体库的外键带来的完整性保障远大于性能开销而且大多数性能问题出在外键列缺少索引而不是外键本身。我的经验是两边都有道理关键看场景。微服务加分库分表的架构下外键基本是失效的数据库层面的引用完整性跨不了多个物理库一致性必须靠应用层事务、消息对账、事件最终一致性这些方案解决这时候坚持用外键没意义。但如果是单体后台管理系统、订单交易系统、内容管理这类一个库能放下的项目外键该加还是要加尤其是一对多关系中“多”的这一端外键能拦住一大类应用层遗漏的bug。不过外键也有两个很实际的坑。第一个是建表顺序必须先建父表再建子表否则会直接报“无法添加外键约束”。第二个是导入数据如果往一张带外键的表中导入大批历史数据可以先关闭外键检查导入完再打开但打开后一定要做一次完整性校验排查有没有孤儿数据。MySQL的写法是SET FOREIGN_KEY_CHECKS 0; -- 批量导入数据 SET FOREIGN_KEY_CHECKS 1;4. 检查约束与默认值低调但关键时刻能兜底的两员大将4.1 CHECK约束“假生效”的版本坑CHECK约束用来限制单列的取值范围或格式比如枚举状态、数值范围、简单字符串格式。但这里藏着一个很多人踩过的大坑MySQL 5.7及更早版本支持写CHECK语法解析却不会执行你写了CHECK (status IN (0,1))插入status 9照样能成功。到了MySQL 8.0.16CHECK约束才真正开始强制生效。MariaDB则稍有不同从10.2.1开始正式执行。这个“版本差”真的很要命。如果你在MySQL 5.7上建了一张表以为CHECK能拦住非法数据其实它除了占个位置什么都没干。老项目从5.7升级到8.0时也要提前知道之前被“宽容”放过的不合法数据不会因为升级自动变干净但升级后新增写入会被严格拦截业务方需要做好对接准备。CHECK约束适合放的场景很明确枚举状态CHECK (status IN (pending, paid, shipped, cancelled))数值范围CHECK (price 0)、CHECK (age BETWEEN 0 AND 120)简单格式CHECK (email LIKE %%)记住CHECK只负责写入时校验这一行的列值是否符合表达式它管不了跨行、跨表的逻辑。像“某个订单的累计金额不能超过用户余额”这种跨表依赖不应该指望CHECK那是应用层事务或触发器该干的事。4.2 DEFAULT默认值时间戳和GUID的两种经典用法默认值负责一件事这条记录创建时如果某个字段没有被传值数据库会自动帮你填上什么。它是最容易被低估的约束之一也是最容易改善数据质量的约束之一。时间戳是最常见的默认值用法MySQLcreate_time DATETIME DEFAULT CURRENT_TIMESTAMP如果需要更新时自动刷新可以写成update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。注意ON UPDATE是MySQL的语法SQL Server没有这种自动更新能力需要触发器配合。SQL Servercreate_time DATETIME DEFAULT GETDATE()。另一个典型用法是给“对外暴露的标识字段”设GUID默认值这也是很多业务里真正需要的SQL Serveruser_guid UNIQUEIDENTIFIER DEFAULT NEWID()或者用NEWSEQUENTIALID()生成更有序的值。MySQL 8.0.13及以上user_guid CHAR(36) DEFAULT (UUID())。PostgreSQL先启用扩展CREATE EXTENSION IF NOT EXISTS pgcrypto;再写user_guid UUID DEFAULT gen_random_uuid()。为什么推荐把“对外GUID”和“内部主键”分开设计因为内部主键用自增整数性能最好而对外GUID可以让接口、订阅系统、防遍历使用即使ID被猜到也不会泄露业务量。两个字段各有各的职责。4.3 “默认值拦不住NULL”这个细节默认值有一个非常容易误解的细节它只在“这一列完全没有出现在INSERT语句里”时生效。如果INSERT语句显式写成了NULL默认值并不会把NULL替换掉除非你用的数据库或写法有特殊处理。所以只要业务上这一列是必须有值的正确姿势一定是“默认值非空约束”组合着写status TINYINT NOT NULL DEFAULT 0 CHECK (status IN (0,1)),这样既能自动填默认值又能拒绝显式传入的NULL。我见过不少表只加了DEFAULT没加NOT NULL然后应用层传了个NULL进来结果NULL照单全收默认值完全没起作用。这个组合是设计新表时最常用的一招写建表语句时可以当成肌肉记忆。5. 约束运行期的三个隐藏雷区索引、命名与修改成本5.1 外键与索引的关系不同数据库表现完全不同先说结论MySQL的InnoDB在创建外键时如果外键列上还没有合适的索引会自动创建一个索引但SQL Server不会自动创建需要你手动在子表的外键列上建索引。很多开发者习惯性以为“外键自带索引”这个结论在不同数据库上会带来完全不同的后果跨库迁移时尤其要注意。不管数据库是否自动创建索引实际开发里我都建议在外键列上显式建立合适索引。这不仅仅是让外键检查更快更是为了让按父表ID查子表的业务查询不要全表扫描。我接手过一个订单查询接口慢得离谱看执行计划发现子表的外键列完全没走索引父表ID一进来就是全表扫加完索引之后查询时间从几秒降到几十毫秒。这个优化跟约束没什么关系纯粹是索引没建对。说到慢SQL优化我要多提醒一句遇到外键关联查询慢先用执行计划确认有没有走索引而不是第一时间怀疑是外键拖慢了性能。外键本身不是慢SQL的元凶那个字段上没有索引才是。5.2 不命名约束的代价建约束时如果不主动起名数据库会自动生成一堆像CONSTRAINT_1、orders_ibfk_1、DF__users__create__3D6E0B2D这种名字。初期没有任何感觉但等你想删某个约束时就得先花半天在各种系统视图里找它到底叫什么。SQL Server给自动生成的默认值约束名尤其恶心中间会夹一串十六进制哈希后缀完全不可读。推荐的做法是建立一套团队级别的命名规范一看到名字就知道约束类型和归属主键PK_表名_字段名如PK_users_id唯一UK_表名_字段名如UK_users_email外键FK_表名_父表名_字段名如FK_orders_users_user_id检查CK_表名_字段名如CK_orders_price默认DF_表名_字段名如DF_users_create_time只要全组都按这个规范走后面任何人查约束时都清清楚楚不需要猜。5.3 修改和删除约束的标准姿势SQL里没有ALTER CONSTRAINT这种一步到位改约束的语法。想调整一个约束必须“先删后加”。拿MySQL为例修改一个外键的动作是ALTER TABLE orders DROP FOREIGN KEY fk_orders_user_id; ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;SQL Server修改默认值约束也是同一个套路ALTER TABLE users DROP CONSTRAINT DF_users_create_time; ALTER TABLE users ADD CONSTRAINT DF_users_create_time DEFAULT GETDATE() FOR create_time;还有一个很常见的卡壳点如果主键被外键引用了直接DROP PRIMARY KEY会失败必须先删除引用它的外键再处理主键。修改主键更是如此依赖关系不处理干净DDL根本执行不下去。想确认当前的约束名两个常用入口记一下MySQL可以用SHOW CREATE TABLE 表名;SQL Server可以查sys.default_constraints和sys.foreign_keys。5.4 约束会不会拖慢写入和查询合理使用约束对查询的影响其实很小查询引擎甚至还能利用约束推导优化执行计划。真正影响写入性能的点在于外键检查需要去子表或父表验证记录批量DML时如果外键列没有索引性能会明显下降同时外键检查还会带来额外的锁竞争。针对这个点我的优化思路很直接用执行计划定位而不是凭感觉猜瓶颈到底在哪索引设计优先覆盖“外键列WHERE高频条件列”大批量导入时MySQL可以临时关掉外键检查导入完再打开并做完整性校验不要因为“觉得慢”就顺手把外键删掉先验证是不是外键检查真的成了瓶颈很多时候建完索引问题就没了。6. 一张包含全部约束的订单表从零开始怎么设计6.1 完整的用户表与订单表建表语句前面几章把约束拆开讲了这一章串起来看一个完整案例。以最常见的电商场景为例设计一张用户表和一张订单表把主键、唯一约束、外键、检查约束、默认值、非空约束全部放进去。用户表CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 内部主键自增, user_guid CHAR(36) NOT NULL COMMENT 对外GUID防止ID遍历, username VARCHAR(50) NOT NULL COMMENT 登录名业务唯一, email VARCHAR(128) NULL COMMENT 邮箱允许为空, phone VARCHAR(20) NULL COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常, 0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_users_user_guid (user_guid), UNIQUE KEY uk_users_username (username), UNIQUE KEY uk_users_email (email), UNIQUE KEY uk_users_phone (phone), CONSTRAINT ck_users_status CHECK (status IN (0,1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;订单表CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单主键, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号全局唯一, user_id BIGINT UNSIGNED NOT NULL COMMENT 下单用户引用users.id, total_amount DECIMAL(10,2) NOT NULL COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, remark VARCHAR(255) NULL COMMENT 备注, expect_arrive_date DATE NULL COMMENT 预计送达日期未确定为NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_orders_order_no (order_no), KEY idx_orders_user_id (user_id), CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT ck_orders_status CHECK (status IN (0,1,2,3,4)), CONSTRAINT ck_orders_total_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;6.2 逐条拆解这样设计的原因先看用户表。主键选了自增id没有用username或phone做主键因为用户名字可以改、手机号可以换只有内部自增ID是稳定不变的。user_guid是给外部接口用的GUID有一层唯一约束这对应前面说过的“对内用整数主键、对外用GUID防遍历”的思路。username、email、phone三条唯一约束都是为了拦住“注册时重复数据”的业务问题。注意email和phone允许NULL这个用例落在MySQL语义下允许多个NULL但如果这套表将来要迁到SQL Server这两个唯一索引会触发“只能一个NULL”的差异迁移前必须评估。订单表里order_no是业务订单号它带了唯一约束但没有当主键。因为订单号在分布式场景下可能包含分库分表位、日期、序号生成规则复杂主键依然用自增ID保持简洁。user_id上建了一个普通索引idx_orders_user_id不是唯一索引——因为一个用户会有多笔订单。这个普通索引同时解决了两个问题按用户查订单走索引外键检查也有索引可用。status字段用一个CHECK约束限定在五个合法状态内。这里我要再强调一下版本问题这套DDL必须在MySQL 8.0.16及以上版本才能真正拦住非法状态如果库里还是5.7这个CHECK形同虚设应用层校验就必须承担全部责任。total_amount的CHECK设为大于等于0避免负金额入库。expect_arrive_date允许NULLNULL表示“还没确定送达时间”。有同学可能会问为什么不加一个“日期不能早于今天”的CHECK我的建议是这类依赖当前时间的校验更适合放应用层。因为不同数据库对CHECK里调用当前日期函数的支持度差异很大很容易产生版本坑数据库约束没必要在这种场景拼极限。6.3 老表补约束的先后顺序建议如果你手上是一张已经运行了很久的老表字段里全是历史数据想补约束不能一下全上否则一条ALTER TABLE可能直接因为存量脏数据执行失败。我的建议是按优先级分步来先补NOT NULL。把那些“逻辑上必须有值”的字段先补上这是改动最小、收益最大的一个。但如果空值已经存在需要先处理存量数据要么补默认值要么清理掉。再补唯一约束。像是手机号、邮箱、订单号这种业务唯一字段补唯一约束前必须跑一遍查重SQL把重复记录合并或清洗掉否则约束根本加不上。然后补外键。补外键前要确认两张表的数据能对得上子表里不能有引用不到父表的孤儿数据。最后再考虑CHECK约束。因为CHECK的版本坑最多先确认数据库版本支持真实执行再评估存量数据是否都满足新加的校验条件。如果存量数据有不满足的可以考虑分两步先修数据再上约束。设计约束这件事我的体感是建表时多花十分钟后面能省上百小时的查数据时间。如果你实在不知道从哪下手就先把NOT NULL和唯一约束补上这两类约束对数据质量的改善最直接、风险也最低。每补一个约束本质上都是在给未来的自己少埋一个坑。