📚 课程目录第4章钻孔数据模型与关系约束

4. 钻孔数据模型与关系约束

钻孔数据库不是把 Excel 表格原样搬进数据库，而是要把“一个钻孔由若干地层段组成、每个地层段占据一个深度区间”这一现实结构，用表结构和约束固化下来。结构设计得好，后续柱状图自动出图只需按深度排序取数；设计得差，出图时就会出现层序颠倒、区间重叠、深度越界等难以排查的问题。本节围绕四件事展开：主键与外键、分层连续性、深度区间校验、完整性规则。

一、主键与外键：把“钻孔—分层”的从属关系写进表结构

推荐的最小模型是三张表：钻孔主表 borehole、地层分层表 stratum、地层字典表 stratum_dict。钻孔主表以钻孔编号为主键，分层表以“钻孔编号 + 层序号”为复合主键，并通过外键指向钻孔主表；分层表的地层代号再通过外键指向地层字典表。这样，一条分层记录必须依附于一个已存在的钻孔，一个地层代号必须是字典中已定义的合法值，从结构上杜绝“孤儿分层”和“野生地层名”。

CREATE TABLE borehole (
  bh_id       VARCHAR(20) PRIMARY KEY,      -- 钻孔编号，如 ZK-01
  x_coord     NUMERIC(12,3) NOT NULL,
  y_coord     NUMERIC(12,3) NOT NULL,
  collar_elev NUMERIC(8,3) NOT NULL,        -- 孔口高程(m)
  final_depth NUMERIC(8,2) NOT NULL CHECK (final_depth > 0),
  drilled_date DATE
);

CREATE TABLE stratum (
  bh_id       VARCHAR(20) NOT NULL,
  layer_no    INTEGER     NOT NULL,         -- 层序号，自上而下 1,2,3...
  top_depth   NUMERIC(8,2) NOT NULL,
  bot_depth   NUMERIC(8,2) NOT NULL,
  stratum_code VARCHAR(10) NOT NULL,
  PRIMARY KEY (bh_id, layer_no),
  FOREIGN KEY (bh_id) REFERENCES borehole(bh_id),
  FOREIGN KEY (stratum_code) REFERENCES stratum_dict(stratum_code),
  CHECK (top_depth >= 0 AND bot_depth > top_depth)
);

这里有两个设计要点。其一，主键用“钻孔编号 + 层序号”而非自增 ID，是因为层序号本身就是业务语义的一部分，柱状图按层序号排序即可还原自上而下的层序，无需额外排序字段。其二，外键必须显式声明，不能只靠应用层“记得检查”。数据库层面的外键在批量导入、脚本补录、多人协作时是最可靠的守门人。

二、地层分层连续性约束：层与层之间不能有缝也不能叠

连续性约束包含三层含义：第一，同一钻孔内，第 1 层的顶深应为 0（以孔口为基准）；第二，第 i 层的底深必须等于第 i+1 层的顶深，即层间无缝、无重叠；第三，最后一层的底深应等于钻孔终孔深度。这三条用普通 CHECK 约束无法跨行表达，需要触发器或导入校验程序实现。

以 PostgreSQL 为例，可在分层表上建立行级触发器，在插入或更新时检查：若 layer_no = 1 则要求 top_depth = 0；否则查询同钻孔 layer_no - 1 的记录，要求其 bot_depth = NEW.top_depth。同时检查不存在 layer_no 更大的记录（防止中间插层导致断链）。若校验不通过，直接 RAISE EXCEPTION 回滚事务。这样，任何一次写入都会维持“0 → 层1底 → 层2底 → … → 终孔深度”的连续链条。

需要说明的是，实际勘察中常出现“缺失地层”（某层在本孔不存在）和“透镜体”（局部夹层）。前者通过不建该层记录表达，不影响连续性；后者应作为独立分层按深度插入，仍满足首尾相接。真正需要禁止的是“同一深度被两层同时占据”或“两层之间留有空档”，这两种情况在柱状图上会表现为色块重叠或空白，是出图异常的高发原因。

三、深度区间校验：单层自身的合法性

单层校验相对简单，但必须做全：顶深不小于 0；底深严格大于顶深（不允许零厚度层）；底深不大于该钻孔的终孔深度；顶深、底深精度统一到 0.01 m，避免 0.1 与 0.10 混存导致比较失真。这些规则可写成 CHECK 约束，也可在导入程序中集中校验。对于回次采取率低、层位判定存疑的层，建议增加“层位可靠性”字段（可靠/推测），在柱状图上用虚线或问号标识，而不是在数据层随意修改深度。

四、数据完整性规则：从“能存”到“可信”

约束解决的是“结构合法”，完整性规则解决的是“业务可信”。建议至少落实以下几条：钻孔编号全局唯一且符合命名规范（如 ZK-01）；坐标、孔口高程、终孔深度不得为空；分层记录数不少于 1；地层代号必须存在于字典表；同一钻孔内层序号连续无跳号；终孔深度与最深分层底深一致。对于跨表一致性（如终孔深度与末层底深），可在导入校验脚本中统一比对，输出差异清单供人工复核，而不是简单拒绝导入——勘察数据常有补测、修正，硬性拒绝会阻断正常作业。

实践中推荐“三道防线”：数据库约束挡住结构性错误；导入程序做跨行、跨表校验并生成问题清单；出图前再做一次只读体检，检查层序、深度范围与字典匹配。三道防线各司其职，既保证数据可信，又不至于让录入人员寸步难行。数据模型与约束一旦稳定，柱状图自动出图就退化为“按 bh_id 取分层、按 layer_no 排序、按深度比例绘制色块”的确定性过程，异常自然大幅减少。