最近不是流行把学习阶段整成修仙境界嘛我加入了一个叫“东方仙盟”的学习社群群里的修炼体系分练气、筑基、金丹看着挺中二但架不住干货多。我的账号卡在“练气期”第一个修炼任务就是用进销存业务把数据库完整性搞透。仔细一想这个组合还真挺讲究——计算机等级考试里“数据库完整性”是二级、三级数据库科目绕不开的高频考点而进销存又是题库里最常出现的业务背景什么商品、供应商、采购单、销售单一摆出来三大完整性约束全都能落地。把这套东西练明白了等于同时打通了考试和真实项目两条路。这篇文章就把我在练气期阶段的完整修炼路径写出来。适合三类人看正在备考全国计算机等级考试NCRE数据库科目的考生、刚学数据库原理但搞不清完整性有什么用的大学生以及准备做进销存、ERP项目但不知道表关系怎么设计才不出错的开发者。内容不绕弯子直接上干货配合进销存的表结构、SQL语句和实际排错过程一步一步拆给你看。1. 练气期的“功法图谱”进销存如何覆盖数据库完整性三大考点1.1 考试大纲里“完整性”三个字背后的真实命题先说考试。计算机等级考试数据库相关科目里对“数据库完整性”的考察从来不是抽象问你“什么是完整性”而是落在这三个点上实体完整性主键必须唯一且非空通俗说就是每张表得有一个能唯一确定记录的“身份标识”。参照完整性外键的值要么为空要么必须等于被参照表中某个已存在的主键值通俗说就是“你引用的数据必须存在不能瞎指”。用户定义完整性针对具体业务规则自定义的约束比如数量不能为负、价格不能超过某个上限、日期必须在合理范围内。选择题里命题人喜欢拿这三条的定义和区分来挖坑操作题里就让你在数据库中建表、设主键、建关系、写约束。如果你只会背概念一旦面对具体业务场景就懵这就是很多考生挂在“数据库操作题”上的原因。1.2 为什么拿进销存练手最顺手进销存就是采购、销售、库存三大块业务串起来的管理系统。它几乎是所有数据库教材、等级考试题库、课程设计里出现频率最高的背景原因很直白实体多、关系复杂、数据变化频繁。一套标准进销存里至少有这些实体供应商、商品、客户、采购入库单、采购入库明细、销售出库单、销售出库明细。这些实体之间存在天然的约束需求。举个例子入库单必须关联一个真实存在的供应商商品编号必须能对应到商品主数据库存数量不可能为负数入库单价不可能小于零。每一个业务规则翻译过来就是一条数据库完整性约束。也正因为如此进销存成了练习数据库完整性最好的“道场”。它不像学生管理系统那样只有一张学生表、一张成绩表而是有完整的上下游关系练起来才有真实感。而且现在“erp进销存手机版”很火大量中小企业在手机上做采购、开单、查库存这些应用后台如果没有扎实的完整性约束撑着手机端录单的时候分分钟出现重复单号、负数库存、找不到客户的脏数据。你在练气期学的东西真不是只能用来应付考试。1.3 我在东方仙盟练气期给自己定的修炼路线既然叫练气期那我也按这个套路走。进销存数据库设计里的三大完整性约束正好可以对应练气期的三个层次第一层实体完整性先把每张表的“丹田”立住也就是主键设计。第二层参照完整性打通表与表之间的“经脉”也就是外键和关系设计。第三层用户定义完整性给数据输入定好“功法规则”也就是CHECK、默认值、非空等约束。每层配合进销存里的具体表结构和SQL练习练完一层再看下一层逻辑非常清晰。下面按这个顺序展开。2. 第一层修炼给进销存每张表“立丹田”——实体完整性2.1 主键选择的关键原则实体完整性的核心就是主键。在进销存系统里主键设计不能拍脑袋我总结三个原则。第一个原则业务单号优先。商品的编号、供应商的编号、客户的编号、入库单号、出库单号这些在真实业务里都是对外可见的单据号往往还是手工录入或扫码输入的。所以它们适合用定长的字符型比如CHAR(6)、CHAR(10)而不是让数据库自动生成的INT AUTO_INCREMENT。自动编号适合做物理主键、内部代理键但如果业务系统要和外部对接或者仓库人员要按单号查找一个稳定、可读、可记忆的业务编号更合适。第二个原则主键字段要极简。如果用一个超长字符串当主键不但索引变大外键引用时也会跟着膨胀影响性能。进销存里的单号一般控制在6到20个字符。第三个原则复合主键要谨慎。进销存里的明细表比如“入库单明细表”一条入库单里有多行商品单号本身不能唯一确定一行记录所以要用(入库单号, 行号)或者(入库单号, 商品编号)做复合主键。这种设计不是不行但要在考题里先看清楚要求如果题目已经给出了“明细序号”字段那基本上就是让你用它和单号一起做主键。2.2 建表与主键的SQL实现我练手时用的是MySQL因为计算机等级考试里二级MySQL一直是很热门的科目。先建两张基础表CREATE TABLE 供应商 ( 供应商编号 CHAR(6) PRIMARY KEY, 供应商名称 VARCHAR(50) NOT NULL, 联系人 VARCHAR(20), 联系电话 VARCHAR(20), 地址 VARCHAR(100) ); CREATE TABLE 商品 ( 商品编号 CHAR(6) PRIMARY KEY, 商品名称 VARCHAR(50) NOT NULL, 分类 VARCHAR(20) DEFAULT 未分类, 规格 VARCHAR(30), 单位 VARCHAR(10), 进货价 DECIMAL(10,2), 销售价 DECIMAL(10,2) );这里PRIMARY KEY就是实体完整性的直接体现。在Access里面更简单打开表设计视图在字段行上右键选择“主键”字段前面会出现一把小钥匙。考试操作题里这步通常是送分点但就是有人会漏掉。再来看明细表的复合主键CREATE TABLE 入库单明细 ( 入库单号 CHAR(10), 行号 INT, 商品编号 CHAR(6) NOT NULL, 数量 INT NOT NULL, 单价 DECIMAL(10,2) NOT NULL, 金额 DECIMAL(12,2), PRIMARY KEY (入库单号, 行号) );复合主键的意义在于防止同一条入库单里出现重复的行号保证每一行明细都能被精确定位。如果你把主键只设在“入库单号”上那一条单号就只能对应一行明细整个入库单就废了。2.3 候选键和唯一约束也是实体完整性的补充实体完整性并不只靠主键。一张表里可能还有其他字段也具备“唯一标识”能力它们在数据库术语里叫候选键。比如供应商表里供应商编号是主键但供应商名称通常也不允许重复否则就会出现两家一模一样的供应商业务上没法区分。这时候需要用UNIQUE约束CREATE TABLE 供应商 ( 供应商编号 CHAR(6) PRIMARY KEY, 供应商名称 VARCHAR(50) NOT NULL UNIQUE, ... );主键和UNIQUE约束都能保证唯一性区别在于一张表只能有一个主键但可以有多个UNIQUE约束主键字段默认NOT NULLUNIQUE字段则允许为空MySQL里一个UNIQUE字段可以有多行NULL。考试里经常拿这个点出选择题别搞混。3. 第二层修炼经脉通畅——参照完整性与外键设计3.1 进销存中的外键关系网参照完整性的关键词是“外键”。在进销存系统里外键关系网大致是下面这个样子子表外键字段被参照表业务含义入库单供应商编号供应商每一笔入库必须对应一个真实供应商入库单明细入库单号入库单明细必须挂在某张入库单下入库单明细商品编号商品明细里不能出现不存在的商品出库单客户编号客户每一笔出库必须对应一个真实客户出库单明细出库单号出库单明细必须挂在某张出库单下出库单明细商品编号商品明细里不能出现不存在的商品库存表商品编号商品库存只针对已建档商品有了这套关系数据库才能在数据写入时自动把关。你手动往入库单里塞一个不存在的供应商编号数据库会直接拒绝这就是参照完整性在工作。3.2 SQL实现与参照动作选择创建外键的SQL标准写法是在建表语句里加CONSTRAINTCREATE TABLE 入库单 ( 入库单号 CHAR(10) PRIMARY KEY, 供应商编号 CHAR(6) NOT NULL, 入库日期 DATE DEFAULT (CURRENT_DATE), 操作员 VARCHAR(20), CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商(供应商编号) ); CREATE TABLE 入库单明细 ( 入库单号 CHAR(10), 行号 INT, 商品编号 CHAR(6) NOT NULL, 数量 INT NOT NULL, 单价 DECIMAL(10,2) NOT NULL, 金额 DECIMAL(12,2), PRIMARY KEY (入库单号, 行号), CONSTRAINT FK_明细_入库单 FOREIGN KEY (入库单号) REFERENCES 入库单(入库单号), CONSTRAINT FK_明细_商品 FOREIGN KEY (商品编号) REFERENCES 商品(商品编号) );外键定义里最容易出题的是“参照动作”。删除或更新被参照表的数据时子表怎么办有几种策略NO ACTION或RESTRICT默认行为直接拒绝删除或更新。如果还有订单引用这个供应商供应商就删不掉。CASCADE级联。删除商品时自动删除所有引用它的明细记录。SET NULL删除被参照记录后子表外键字段自动置空。进销存业务里采购、销售单据都属于业务留痕数据不能因为主数据删了就跟着没否则审计的时候全乱套。所以生产环境最常用的是NO ACTION宁可删不掉也不能静默删单。考试里如果题目要求“删除供应商时相关入库单一起删除”才需要写ON DELETE CASCADE否则老老实实默认就行。CREATE TABLE 入库单 ( 入库单号 CHAR(10) PRIMARY KEY, 供应商编号 CHAR(6) NOT NULL, 入库日期 DATE, CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商(供应商编号) ON DELETE NO ACTION ON UPDATE CASCADE );这里我把ON UPDATE设成了CASCADE意思是如果哪天供应商编号要改号所有入库单里的编号跟着自动更新。这种组合在真实业务里很实用。3.3 考试里参照完整性的操作题要点如果是Access操作题做参照完整性的路径是关闭所有表进入“数据库工具”里的“关系”窗口把“供应商”表的“供应商编号”拖到“入库单”表的“供应商编号”上在弹出的编辑关系对话框里勾选“实施参照完整性”。如果想设置级联再勾选“级联更新相关字段”和“级联删除相关记录”。这里有个高频踩坑点两表的关联字段类型必须一致。一个字段是文本另一个是数字拖动关系时Access会报错或者建立了但无法实施参照完整性。所以建表阶段就要统一编号字段的类型和长度。考试的操作题平台对这种细节盯得很紧字段类型不一致后面关系就全白做。4. 第三层修炼规则不跑偏——用户定义完整性4.1 CHECK约束映射业务规则用户定义完整性是针对具体业务的约束在SQL里最直接的表现是CHECK。进销存系统里典型的业务规则有这些库存数量不能小于0。入库数量、出库数量必须大于0。单价、金额不能为负数。进货价和销售价要在一个合理区间。折扣不能超过100%。在建表时顺手加上这些约束CREATE TABLE 库存 ( 商品编号 CHAR(6) PRIMARY KEY, 库存数量 INT NOT NULL, 库存下限 INT DEFAULT 0, CONSTRAINT CK_库存数量 CHECK (库存数量 0) ); CREATE TABLE 商品 ( 商品编号 CHAR(6) PRIMARY KEY, 商品名称 VARCHAR(50) NOT NULL, 销售价 DECIMAL(10,2), 进货价 DECIMAL(10,2), CONSTRAINT CK_价格 CHECK (进货价 0 AND 销售价 0) );在Access里对应的操作是在表设计视图的“有效性规则”栏中填表达式比如0再在“有效性文本”里写提示文字“库存数量不能小于0”。这个字段就是操作题的常客既简单又容易漏。4.2 默认值、非空约束与数据类型选择用户定义完整性不只是CHECK还包括默认值、非空约束和数据类型的选择。默认值很有用。入库日期一般默认取系统当天订单状态默认“待审核”分类默认“未分类”。在SQL里写DEFAULT (CURRENT_DATE)、DEFAULT 待审核就行。这在考试选择题里会考在真实项目里也是懒人福音少录一个字段就少一次出错机会。非空约束也很关键。像入库单号、供应商编号、商品编号、数量、单价这些核心字段业务上不可能为空建表时一定要写成NOT NULL。有些考生图省事不写结果数据录入时出现一堆“半个单子”这在实际系统里是要出大事的。数据类型选择更是一个经典考点。金额字段必须用DECIMAL(10,2)不要用FLOAT、DOUBLE。原因很简单浮点数在计算机里是近似存储的0.1加0.2算出来可能是0.30000000000000004金额一旦出现精度问题对账就对不上。考试里如果题目表结构给了“金额 NUMERIC(8,2)”建表时照着写局部变量也记得用DECIMAL。4.3 触发器用户定义完整性的高级形态当业务规则复杂到CHECK表达不了的时候就轮到触发器上场了。比如进销存里的经典需求出库单明细插入后库存要同步减少如果库存不足整笔操作回滚。这个用CHECK做不到因为涉及多张表的联动。MySQL里的简单示例DELIMITER // CREATE TRIGGER trg_出库扣库存 AFTER INSERT ON 出库单明细 FOR EACH ROW BEGIN UPDATE 库存 SET 库存数量 库存数量 - NEW.数量 WHERE 商品编号 NEW.商品编号; IF (SELECT 库存数量 FROM 库存 WHERE 商品编号 NEW.商品编号) 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足禁止出库; END IF; END// DELIMITER ;这个触发器在计算机等级考试三级数据库以及部分二级MySQL题库里会出现。你要记住的是触发器属于用户定义完整性的延伸它能在业务层面做数据库自动完成的规则校验。练气期阶段不用写太复杂的触发器但能看懂、会鉴别已经能甩开一批考生了。5. 考试视角从练气期到考场的答题模板5.1 选择题高频概念对照表在选择题里命题人最喜欢把三大完整性混在一起考。我总结了一张快速判断表考前背下来很管用题干特征对应完整性主键字段不能为空、不能重复实体完整性外键值必须等于被参照表主键值或为空参照完整性“性别只能取男或女”、“年龄在0到150之间”用户定义完整性删除父表记录时子表级联删除参照完整性中的级联策略一个字段不允许为空、不允许重复实体完整性非空唯一这类题的陷阱主要在“用户定义完整性”上。很多人一看到“不能为空”就选实体完整性但“不能为空”本身可能是实体完整性主键非空也可能是用户定义完整性电话号码不能为空但又不是主键。判断标准是这个约束是不是针对具体业务场景自定义的。纯粹为了保证主键唯一非空是实体完整性针对一个普通字段规定取值规则就得归到用户定义完整性。5.2 操作题完整流程从“表结构”到“可运行的约束集合”操作题最稳妥的答题顺序是固定的。以下以MySQL数据库考试平台为例。第一步先建基础表也就是供应商表、客户表、商品表设置主键。第二步建单据表和明细表设置复合主键和所有外键。第三步补充CHECK、默认值、非空约束。第四步录入测试数据故意插入几条违反约束的数据验证约束生效。验证这一步特别重要。比如你执行下面这条语句INSERT INTO 入库单 (入库单号, 供应商编号) VALUES (RK20250001, GYS999);如果供应商表里没有GYS999数据库会返回类似下面的错误ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test.入库单, CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商 (供应商编号))看到这个报错就说明你的外键写对了。操作题里有些平台会要求你写出这种“验证约束是否生效”的步骤你只要把报错信息截进去或者描述清楚就能拿到分。5.3 判分点与最容易丢分的小细节结合我在备考群里围观别人提交的作业归纳一下操作题常见的丢分点漏设主键或设错主键这是致命的整张表白建。字段名跟题目要求不一致大小写、下划线都要看仔细。金额用FLOAT而题目要求DECIMAL一分都拿不到。外键命名不规范虽然不报错但阅卷时按“是否写明约束名”给分的情况不少。Access里画了关系但是没勾“实施参照完整性”等于白画。MySQL里用了MyISAM引擎写外键结果外键根本没生效。考试平台一般默认InnoDB但你要知道MyISAM不支持外键这是二级MySQL里反复考的概念。建表顺序错了先建明细表后建主表外键会报“无法创建”因为被参照的表还不存在。这些细节看着小实际丢分最狠。6. 练气期踩坑实录一次插入失败背后的完整排查链路6.1 场景复现出库单明细就是插不进去我在练气期做综合练习时遇到过一个非常有代表性的问题。当时我建好了客户表、商品表、出库单表、出库单明细表也在明细表上设了两个外键。然后执行INSERT INTO 出库单 (出库单号, 客户编号) VALUES (CK20250001, KH001); INSERT INTO 出库单明细 (出库单号, 行号, 商品编号, 数量, 单价) VALUES (CK20250001, 1, SP005, 10, 25.00);第一条插入很正常第二条直接报错ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails我当时的第一反应是“商品编号写错了”。但查了商品表SP005确实存在。这个现象很有意思也很有迷惑性。6.2 逐步排查从报错信息倒查约束遇到这种问题我建议按下面的顺序排查。第一步仔细看报错信息里的约束名。MySQL的报错会明确指出违反的是哪个约束。如果约束名是FK_明细_商品那就锁定商品表如果是FK_明细_出库单那就是出库单主表的问题。第二步检查被参照表里到底有没有这条记录。用SELECT * FROM 商品 WHERE 商品编号 SP005;确认存在这一步我做了记录在。第三步检查字段类型是否一致。这一步被我一开始忽略了后来查表结构才发现出库单明细表里的商品编号是CHAR(6)商品表里的商品编号也是CHAR(6)看起来没问题。但是我又检查了字符集和排序规则发现商品表用的utf8mb4_general_ci明细表用的utf8mb4_unicode_ci虽然一般不影响等值判断但有些极端情况下索引匹配会出幺蛾子。这不是我这次报错的原因但提醒大家注意。第四步检查是不是事务或删除操作导致的问题。我的场景里没有删除所以排除。第五步检查存储引擎。这是我最终锁定的问题。我用SHOW TABLE STATUS LIKE 出库单明细;一看引擎显示MyISAM。MyISAM根本不检查外键约束但诡异的是它居然报了1452外键错误其实不是外键本身报错而是我手动加了FOREIGN KEY语法MyISAM会“记住”这个定义但它不执行真正的报错其实是出在了别处。后来我干脆把两张表都改成InnoDB重建外键问题彻底消失。6.3 修复方案与三条实战经验修复方案分两层。数据层如果是因为主表缺少记录先插入主表再插明细如果是因为类型不一致统一字段类型和长度。结构层保证两张表都是InnoDB确认外键字段和被引用主键的类型、字符集完全一致必要时ALTER TABLE重建外键。这次踩坑给我留下三条经验。第一条建表前先选好存储引擎考试和项目里都用InnoDB别用MyISAM。第二条外键字段和被引用字段必须完全同类型不只是“看起来差不多”CHAR长度、字符集、排序规则都要看。第三条MySQL报1452时第一反应去查主表记录是否存在第二反应查类型第三反应查引擎按这个顺序能少走弯路。6.4 备考中同样容易栽的“隐形坑”顺手再提醒三个容易踩的隐形坑。第一个是Access里“实施了参照完整性”但没勾“级联更新”当你要修改主表主键值时会被卡住考试时如果题目要求改编号记得回去把“级联更新相关字段”勾上。第二个是外键字段允许为空导致录入时“漏关联”。外键可以为空在数据库层面是合法的但业务上很多外键字段不该为空建表时要用NOT NULL把它卡住否则一堆出库单没有客户编号统计报表全是脏数据。第三个是触发器里的递归调用。如果你在入库明细上写了更新库存的触发器库存表上又写了反向更新明细的触发器两边互相触发数据库直接报栈溢出。进销存系统里这种写法非常危险练手时要注意。最后再分享一个我自己的小习惯每张表建完后我会故意插入一条非法数据来“测试约束”比如往商品表插一个重复主键往出库单明细插一个不存在的商品编号往库存表插入一个负数库存。看到数据库把这些数据统统拒掉心里才算踏实。这个习惯伴随我从练气期一路走到现在每次做完一个数据库项目我都会回头把完整性约束检查一遍确认它们是真的在工作而不是只存在于文档里。进销存这个业务模型之所以经典就是因为它能把数据库完整性所有知识点串成一条线。如果你也在备考计算机等级考试或者正打算做一个进销存系统我建议你从练气期的这三层开始一层一层打通后面再看什么外键报错、约束失效的问题都会从容很多。
进销存实战：从主键外键到CHECK约束，吃透数据库完整性
最近不是流行把学习阶段整成修仙境界嘛我加入了一个叫“东方仙盟”的学习社群群里的修炼体系分练气、筑基、金丹看着挺中二但架不住干货多。我的账号卡在“练气期”第一个修炼任务就是用进销存业务把数据库完整性搞透。仔细一想这个组合还真挺讲究——计算机等级考试里“数据库完整性”是二级、三级数据库科目绕不开的高频考点而进销存又是题库里最常出现的业务背景什么商品、供应商、采购单、销售单一摆出来三大完整性约束全都能落地。把这套东西练明白了等于同时打通了考试和真实项目两条路。这篇文章就把我在练气期阶段的完整修炼路径写出来。适合三类人看正在备考全国计算机等级考试NCRE数据库科目的考生、刚学数据库原理但搞不清完整性有什么用的大学生以及准备做进销存、ERP项目但不知道表关系怎么设计才不出错的开发者。内容不绕弯子直接上干货配合进销存的表结构、SQL语句和实际排错过程一步一步拆给你看。1. 练气期的“功法图谱”进销存如何覆盖数据库完整性三大考点1.1 考试大纲里“完整性”三个字背后的真实命题先说考试。计算机等级考试数据库相关科目里对“数据库完整性”的考察从来不是抽象问你“什么是完整性”而是落在这三个点上实体完整性主键必须唯一且非空通俗说就是每张表得有一个能唯一确定记录的“身份标识”。参照完整性外键的值要么为空要么必须等于被参照表中某个已存在的主键值通俗说就是“你引用的数据必须存在不能瞎指”。用户定义完整性针对具体业务规则自定义的约束比如数量不能为负、价格不能超过某个上限、日期必须在合理范围内。选择题里命题人喜欢拿这三条的定义和区分来挖坑操作题里就让你在数据库中建表、设主键、建关系、写约束。如果你只会背概念一旦面对具体业务场景就懵这就是很多考生挂在“数据库操作题”上的原因。1.2 为什么拿进销存练手最顺手进销存就是采购、销售、库存三大块业务串起来的管理系统。它几乎是所有数据库教材、等级考试题库、课程设计里出现频率最高的背景原因很直白实体多、关系复杂、数据变化频繁。一套标准进销存里至少有这些实体供应商、商品、客户、采购入库单、采购入库明细、销售出库单、销售出库明细。这些实体之间存在天然的约束需求。举个例子入库单必须关联一个真实存在的供应商商品编号必须能对应到商品主数据库存数量不可能为负数入库单价不可能小于零。每一个业务规则翻译过来就是一条数据库完整性约束。也正因为如此进销存成了练习数据库完整性最好的“道场”。它不像学生管理系统那样只有一张学生表、一张成绩表而是有完整的上下游关系练起来才有真实感。而且现在“erp进销存手机版”很火大量中小企业在手机上做采购、开单、查库存这些应用后台如果没有扎实的完整性约束撑着手机端录单的时候分分钟出现重复单号、负数库存、找不到客户的脏数据。你在练气期学的东西真不是只能用来应付考试。1.3 我在东方仙盟练气期给自己定的修炼路线既然叫练气期那我也按这个套路走。进销存数据库设计里的三大完整性约束正好可以对应练气期的三个层次第一层实体完整性先把每张表的“丹田”立住也就是主键设计。第二层参照完整性打通表与表之间的“经脉”也就是外键和关系设计。第三层用户定义完整性给数据输入定好“功法规则”也就是CHECK、默认值、非空等约束。每层配合进销存里的具体表结构和SQL练习练完一层再看下一层逻辑非常清晰。下面按这个顺序展开。2. 第一层修炼给进销存每张表“立丹田”——实体完整性2.1 主键选择的关键原则实体完整性的核心就是主键。在进销存系统里主键设计不能拍脑袋我总结三个原则。第一个原则业务单号优先。商品的编号、供应商的编号、客户的编号、入库单号、出库单号这些在真实业务里都是对外可见的单据号往往还是手工录入或扫码输入的。所以它们适合用定长的字符型比如CHAR(6)、CHAR(10)而不是让数据库自动生成的INT AUTO_INCREMENT。自动编号适合做物理主键、内部代理键但如果业务系统要和外部对接或者仓库人员要按单号查找一个稳定、可读、可记忆的业务编号更合适。第二个原则主键字段要极简。如果用一个超长字符串当主键不但索引变大外键引用时也会跟着膨胀影响性能。进销存里的单号一般控制在6到20个字符。第三个原则复合主键要谨慎。进销存里的明细表比如“入库单明细表”一条入库单里有多行商品单号本身不能唯一确定一行记录所以要用(入库单号, 行号)或者(入库单号, 商品编号)做复合主键。这种设计不是不行但要在考题里先看清楚要求如果题目已经给出了“明细序号”字段那基本上就是让你用它和单号一起做主键。2.2 建表与主键的SQL实现我练手时用的是MySQL因为计算机等级考试里二级MySQL一直是很热门的科目。先建两张基础表CREATE TABLE 供应商 ( 供应商编号 CHAR(6) PRIMARY KEY, 供应商名称 VARCHAR(50) NOT NULL, 联系人 VARCHAR(20), 联系电话 VARCHAR(20), 地址 VARCHAR(100) ); CREATE TABLE 商品 ( 商品编号 CHAR(6) PRIMARY KEY, 商品名称 VARCHAR(50) NOT NULL, 分类 VARCHAR(20) DEFAULT 未分类, 规格 VARCHAR(30), 单位 VARCHAR(10), 进货价 DECIMAL(10,2), 销售价 DECIMAL(10,2) );这里PRIMARY KEY就是实体完整性的直接体现。在Access里面更简单打开表设计视图在字段行上右键选择“主键”字段前面会出现一把小钥匙。考试操作题里这步通常是送分点但就是有人会漏掉。再来看明细表的复合主键CREATE TABLE 入库单明细 ( 入库单号 CHAR(10), 行号 INT, 商品编号 CHAR(6) NOT NULL, 数量 INT NOT NULL, 单价 DECIMAL(10,2) NOT NULL, 金额 DECIMAL(12,2), PRIMARY KEY (入库单号, 行号) );复合主键的意义在于防止同一条入库单里出现重复的行号保证每一行明细都能被精确定位。如果你把主键只设在“入库单号”上那一条单号就只能对应一行明细整个入库单就废了。2.3 候选键和唯一约束也是实体完整性的补充实体完整性并不只靠主键。一张表里可能还有其他字段也具备“唯一标识”能力它们在数据库术语里叫候选键。比如供应商表里供应商编号是主键但供应商名称通常也不允许重复否则就会出现两家一模一样的供应商业务上没法区分。这时候需要用UNIQUE约束CREATE TABLE 供应商 ( 供应商编号 CHAR(6) PRIMARY KEY, 供应商名称 VARCHAR(50) NOT NULL UNIQUE, ... );主键和UNIQUE约束都能保证唯一性区别在于一张表只能有一个主键但可以有多个UNIQUE约束主键字段默认NOT NULLUNIQUE字段则允许为空MySQL里一个UNIQUE字段可以有多行NULL。考试里经常拿这个点出选择题别搞混。3. 第二层修炼经脉通畅——参照完整性与外键设计3.1 进销存中的外键关系网参照完整性的关键词是“外键”。在进销存系统里外键关系网大致是下面这个样子子表外键字段被参照表业务含义入库单供应商编号供应商每一笔入库必须对应一个真实供应商入库单明细入库单号入库单明细必须挂在某张入库单下入库单明细商品编号商品明细里不能出现不存在的商品出库单客户编号客户每一笔出库必须对应一个真实客户出库单明细出库单号出库单明细必须挂在某张出库单下出库单明细商品编号商品明细里不能出现不存在的商品库存表商品编号商品库存只针对已建档商品有了这套关系数据库才能在数据写入时自动把关。你手动往入库单里塞一个不存在的供应商编号数据库会直接拒绝这就是参照完整性在工作。3.2 SQL实现与参照动作选择创建外键的SQL标准写法是在建表语句里加CONSTRAINTCREATE TABLE 入库单 ( 入库单号 CHAR(10) PRIMARY KEY, 供应商编号 CHAR(6) NOT NULL, 入库日期 DATE DEFAULT (CURRENT_DATE), 操作员 VARCHAR(20), CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商(供应商编号) ); CREATE TABLE 入库单明细 ( 入库单号 CHAR(10), 行号 INT, 商品编号 CHAR(6) NOT NULL, 数量 INT NOT NULL, 单价 DECIMAL(10,2) NOT NULL, 金额 DECIMAL(12,2), PRIMARY KEY (入库单号, 行号), CONSTRAINT FK_明细_入库单 FOREIGN KEY (入库单号) REFERENCES 入库单(入库单号), CONSTRAINT FK_明细_商品 FOREIGN KEY (商品编号) REFERENCES 商品(商品编号) );外键定义里最容易出题的是“参照动作”。删除或更新被参照表的数据时子表怎么办有几种策略NO ACTION或RESTRICT默认行为直接拒绝删除或更新。如果还有订单引用这个供应商供应商就删不掉。CASCADE级联。删除商品时自动删除所有引用它的明细记录。SET NULL删除被参照记录后子表外键字段自动置空。进销存业务里采购、销售单据都属于业务留痕数据不能因为主数据删了就跟着没否则审计的时候全乱套。所以生产环境最常用的是NO ACTION宁可删不掉也不能静默删单。考试里如果题目要求“删除供应商时相关入库单一起删除”才需要写ON DELETE CASCADE否则老老实实默认就行。CREATE TABLE 入库单 ( 入库单号 CHAR(10) PRIMARY KEY, 供应商编号 CHAR(6) NOT NULL, 入库日期 DATE, CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商(供应商编号) ON DELETE NO ACTION ON UPDATE CASCADE );这里我把ON UPDATE设成了CASCADE意思是如果哪天供应商编号要改号所有入库单里的编号跟着自动更新。这种组合在真实业务里很实用。3.3 考试里参照完整性的操作题要点如果是Access操作题做参照完整性的路径是关闭所有表进入“数据库工具”里的“关系”窗口把“供应商”表的“供应商编号”拖到“入库单”表的“供应商编号”上在弹出的编辑关系对话框里勾选“实施参照完整性”。如果想设置级联再勾选“级联更新相关字段”和“级联删除相关记录”。这里有个高频踩坑点两表的关联字段类型必须一致。一个字段是文本另一个是数字拖动关系时Access会报错或者建立了但无法实施参照完整性。所以建表阶段就要统一编号字段的类型和长度。考试的操作题平台对这种细节盯得很紧字段类型不一致后面关系就全白做。4. 第三层修炼规则不跑偏——用户定义完整性4.1 CHECK约束映射业务规则用户定义完整性是针对具体业务的约束在SQL里最直接的表现是CHECK。进销存系统里典型的业务规则有这些库存数量不能小于0。入库数量、出库数量必须大于0。单价、金额不能为负数。进货价和销售价要在一个合理区间。折扣不能超过100%。在建表时顺手加上这些约束CREATE TABLE 库存 ( 商品编号 CHAR(6) PRIMARY KEY, 库存数量 INT NOT NULL, 库存下限 INT DEFAULT 0, CONSTRAINT CK_库存数量 CHECK (库存数量 0) ); CREATE TABLE 商品 ( 商品编号 CHAR(6) PRIMARY KEY, 商品名称 VARCHAR(50) NOT NULL, 销售价 DECIMAL(10,2), 进货价 DECIMAL(10,2), CONSTRAINT CK_价格 CHECK (进货价 0 AND 销售价 0) );在Access里对应的操作是在表设计视图的“有效性规则”栏中填表达式比如0再在“有效性文本”里写提示文字“库存数量不能小于0”。这个字段就是操作题的常客既简单又容易漏。4.2 默认值、非空约束与数据类型选择用户定义完整性不只是CHECK还包括默认值、非空约束和数据类型的选择。默认值很有用。入库日期一般默认取系统当天订单状态默认“待审核”分类默认“未分类”。在SQL里写DEFAULT (CURRENT_DATE)、DEFAULT 待审核就行。这在考试选择题里会考在真实项目里也是懒人福音少录一个字段就少一次出错机会。非空约束也很关键。像入库单号、供应商编号、商品编号、数量、单价这些核心字段业务上不可能为空建表时一定要写成NOT NULL。有些考生图省事不写结果数据录入时出现一堆“半个单子”这在实际系统里是要出大事的。数据类型选择更是一个经典考点。金额字段必须用DECIMAL(10,2)不要用FLOAT、DOUBLE。原因很简单浮点数在计算机里是近似存储的0.1加0.2算出来可能是0.30000000000000004金额一旦出现精度问题对账就对不上。考试里如果题目表结构给了“金额 NUMERIC(8,2)”建表时照着写局部变量也记得用DECIMAL。4.3 触发器用户定义完整性的高级形态当业务规则复杂到CHECK表达不了的时候就轮到触发器上场了。比如进销存里的经典需求出库单明细插入后库存要同步减少如果库存不足整笔操作回滚。这个用CHECK做不到因为涉及多张表的联动。MySQL里的简单示例DELIMITER // CREATE TRIGGER trg_出库扣库存 AFTER INSERT ON 出库单明细 FOR EACH ROW BEGIN UPDATE 库存 SET 库存数量 库存数量 - NEW.数量 WHERE 商品编号 NEW.商品编号; IF (SELECT 库存数量 FROM 库存 WHERE 商品编号 NEW.商品编号) 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足禁止出库; END IF; END// DELIMITER ;这个触发器在计算机等级考试三级数据库以及部分二级MySQL题库里会出现。你要记住的是触发器属于用户定义完整性的延伸它能在业务层面做数据库自动完成的规则校验。练气期阶段不用写太复杂的触发器但能看懂、会鉴别已经能甩开一批考生了。5. 考试视角从练气期到考场的答题模板5.1 选择题高频概念对照表在选择题里命题人最喜欢把三大完整性混在一起考。我总结了一张快速判断表考前背下来很管用题干特征对应完整性主键字段不能为空、不能重复实体完整性外键值必须等于被参照表主键值或为空参照完整性“性别只能取男或女”、“年龄在0到150之间”用户定义完整性删除父表记录时子表级联删除参照完整性中的级联策略一个字段不允许为空、不允许重复实体完整性非空唯一这类题的陷阱主要在“用户定义完整性”上。很多人一看到“不能为空”就选实体完整性但“不能为空”本身可能是实体完整性主键非空也可能是用户定义完整性电话号码不能为空但又不是主键。判断标准是这个约束是不是针对具体业务场景自定义的。纯粹为了保证主键唯一非空是实体完整性针对一个普通字段规定取值规则就得归到用户定义完整性。5.2 操作题完整流程从“表结构”到“可运行的约束集合”操作题最稳妥的答题顺序是固定的。以下以MySQL数据库考试平台为例。第一步先建基础表也就是供应商表、客户表、商品表设置主键。第二步建单据表和明细表设置复合主键和所有外键。第三步补充CHECK、默认值、非空约束。第四步录入测试数据故意插入几条违反约束的数据验证约束生效。验证这一步特别重要。比如你执行下面这条语句INSERT INTO 入库单 (入库单号, 供应商编号) VALUES (RK20250001, GYS999);如果供应商表里没有GYS999数据库会返回类似下面的错误ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test.入库单, CONSTRAINT FK_入库单_供应商 FOREIGN KEY (供应商编号) REFERENCES 供应商 (供应商编号))看到这个报错就说明你的外键写对了。操作题里有些平台会要求你写出这种“验证约束是否生效”的步骤你只要把报错信息截进去或者描述清楚就能拿到分。5.3 判分点与最容易丢分的小细节结合我在备考群里围观别人提交的作业归纳一下操作题常见的丢分点漏设主键或设错主键这是致命的整张表白建。字段名跟题目要求不一致大小写、下划线都要看仔细。金额用FLOAT而题目要求DECIMAL一分都拿不到。外键命名不规范虽然不报错但阅卷时按“是否写明约束名”给分的情况不少。Access里画了关系但是没勾“实施参照完整性”等于白画。MySQL里用了MyISAM引擎写外键结果外键根本没生效。考试平台一般默认InnoDB但你要知道MyISAM不支持外键这是二级MySQL里反复考的概念。建表顺序错了先建明细表后建主表外键会报“无法创建”因为被参照的表还不存在。这些细节看着小实际丢分最狠。6. 练气期踩坑实录一次插入失败背后的完整排查链路6.1 场景复现出库单明细就是插不进去我在练气期做综合练习时遇到过一个非常有代表性的问题。当时我建好了客户表、商品表、出库单表、出库单明细表也在明细表上设了两个外键。然后执行INSERT INTO 出库单 (出库单号, 客户编号) VALUES (CK20250001, KH001); INSERT INTO 出库单明细 (出库单号, 行号, 商品编号, 数量, 单价) VALUES (CK20250001, 1, SP005, 10, 25.00);第一条插入很正常第二条直接报错ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails我当时的第一反应是“商品编号写错了”。但查了商品表SP005确实存在。这个现象很有意思也很有迷惑性。6.2 逐步排查从报错信息倒查约束遇到这种问题我建议按下面的顺序排查。第一步仔细看报错信息里的约束名。MySQL的报错会明确指出违反的是哪个约束。如果约束名是FK_明细_商品那就锁定商品表如果是FK_明细_出库单那就是出库单主表的问题。第二步检查被参照表里到底有没有这条记录。用SELECT * FROM 商品 WHERE 商品编号 SP005;确认存在这一步我做了记录在。第三步检查字段类型是否一致。这一步被我一开始忽略了后来查表结构才发现出库单明细表里的商品编号是CHAR(6)商品表里的商品编号也是CHAR(6)看起来没问题。但是我又检查了字符集和排序规则发现商品表用的utf8mb4_general_ci明细表用的utf8mb4_unicode_ci虽然一般不影响等值判断但有些极端情况下索引匹配会出幺蛾子。这不是我这次报错的原因但提醒大家注意。第四步检查是不是事务或删除操作导致的问题。我的场景里没有删除所以排除。第五步检查存储引擎。这是我最终锁定的问题。我用SHOW TABLE STATUS LIKE 出库单明细;一看引擎显示MyISAM。MyISAM根本不检查外键约束但诡异的是它居然报了1452外键错误其实不是外键本身报错而是我手动加了FOREIGN KEY语法MyISAM会“记住”这个定义但它不执行真正的报错其实是出在了别处。后来我干脆把两张表都改成InnoDB重建外键问题彻底消失。6.3 修复方案与三条实战经验修复方案分两层。数据层如果是因为主表缺少记录先插入主表再插明细如果是因为类型不一致统一字段类型和长度。结构层保证两张表都是InnoDB确认外键字段和被引用主键的类型、字符集完全一致必要时ALTER TABLE重建外键。这次踩坑给我留下三条经验。第一条建表前先选好存储引擎考试和项目里都用InnoDB别用MyISAM。第二条外键字段和被引用字段必须完全同类型不只是“看起来差不多”CHAR长度、字符集、排序规则都要看。第三条MySQL报1452时第一反应去查主表记录是否存在第二反应查类型第三反应查引擎按这个顺序能少走弯路。6.4 备考中同样容易栽的“隐形坑”顺手再提醒三个容易踩的隐形坑。第一个是Access里“实施了参照完整性”但没勾“级联更新”当你要修改主表主键值时会被卡住考试时如果题目要求改编号记得回去把“级联更新相关字段”勾上。第二个是外键字段允许为空导致录入时“漏关联”。外键可以为空在数据库层面是合法的但业务上很多外键字段不该为空建表时要用NOT NULL把它卡住否则一堆出库单没有客户编号统计报表全是脏数据。第三个是触发器里的递归调用。如果你在入库明细上写了更新库存的触发器库存表上又写了反向更新明细的触发器两边互相触发数据库直接报栈溢出。进销存系统里这种写法非常危险练手时要注意。最后再分享一个我自己的小习惯每张表建完后我会故意插入一条非法数据来“测试约束”比如往商品表插一个重复主键往出库单明细插一个不存在的商品编号往库存表插入一个负数库存。看到数据库把这些数据统统拒掉心里才算踏实。这个习惯伴随我从练气期一路走到现在每次做完一个数据库项目我都会回头把完整性约束检查一遍确认它们是真的在工作而不是只存在于文档里。进销存这个业务模型之所以经典就是因为它能把数据库完整性所有知识点串成一条线。如果你也在备考计算机等级考试或者正打算做一个进销存系统我建议你从练气期的这三层开始一层一层打通后面再看什么外键报错、约束失效的问题都会从容很多。
