Firebird 2.5复合主键表Before Insert触发器异常与主键冲突问题

维护的基于Firebird 2.5（Ubuntu 18.04服务器）的遗留Web应用近期出现异常，涉及表STAT_VALIDATION：

表结构与约束 #

/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE TABLE STAT_VALIDATION (
    DATAREPORTID  D$BIGINT NOT NULL /* D$BIGINT = BIGINT */,
    PARAMGROUPID  D$INTEGER NOT NULL /* D$INTEGER = INTEGER */,
    KINDID        D$BIGINT NOT NULL /* D$BIGINT = BIGINT */,
    VALIDATIONID  D$BIGINT NOT NULL /* D$BIGINT = BIGINT */,
    ARG1          U$VARCHAR500 NOT NULL /* U$VARCHAR500 = VARCHAR(500) */,
    ARG2          U$VARCHAR500 /* U$VARCHAR500 = VARCHAR(500) */,
    NOTE          U$TEXTBLOB NOT NULL /* U$TEXTBLOB = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
);
/******************************************************************************/
/*                                Primary keys                                */
/******************************************************************************/
ALTER TABLE STAT_VALIDATION ADD CONSTRAINT PK_STAT_VALIDATION PRIMARY KEY (DATAREPORTID, PARAMGROUPID, KINDID, VALIDATIONID);
/******************************************************************************/
/*                                Foreign keys                                */
/******************************************************************************/
ALTER TABLE STAT_VALIDATION ADD CONSTRAINT FK_STAT_VALIDATION_DATAREPORTID FOREIGN KEY (DATAREPORTID, PARAMGROUPID, KINDID) REFERENCES STAT_DATA (DATAREPORTID, PARAMGROUPID, KINDID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE STAT_VALIDATION ADD CONSTRAINT FK_STAT_VALIDATION_VALIDATIONID FOREIGN KEY (VALIDATIONID) REFERENCES CONTENTTREE (NODEID) ON UPDATE CASCADE;

前置触发器逻辑 #

表上有BEFORE INSERT触发器STAT_VALIDATION_BI0，用于检查复合主键组合是否已存在，存在则抛自定义异常：

/* Trigger: STAT_VALIDATION_BI0 */
CREATE OR ALTER TRIGGER STAT_VALIDATION_BI0 FOR STAT_VALIDATION
ACTIVE BEFORE INSERT POSITION 0
as
declare variable PARAMS D$VARCHAR500;
begin
  if (exists (
    select *
    from STAT_VALIDATION SV
    where SV.DATAREPORTID = new.DATAREPORTID
    and SV.PARAMGROUPID = new.PARAMGROUPID
    and SV.KINDID = new.KINDID
    and SV.VALIDATIONID = new.VALIDATIONID
  )) then
  begin
    select list(VSPG.PARAMNAME || '=' || VSPG.PARAMVALUE, ';')
    from V_STAT_PARAMGROUPS VSPG
    where VSPG.PARAMGROUPID = new.PARAMGROUPID
    into :PARAMS;

    exception E_CUSTOM_EXCEPTION 'Validation already exists: '
      || 'DATAREPORTID=' || new.DATAREPORTID
      || '; PARAMGROUPID=' || new.PARAMGROUPID
      || '; KINDID=' || new.KINDID
      || '; VALIDATIONID=' || new.VALIDATIONID
      || '; PARAMS: ' || :PARAMS;
  end
end

异常现象 #

尝试插入记录DATAREPORTID=214746; PARAMGROUPID=45542; KINDID=48062; VALIDATIONID=99517时：

• 触发器触发自定义异常，提示记录已存在；
• 手动执行触发器内的查询语句，未找到对应记录；单独查询DATAREPORTID=214746也无结果；仅PARAMGROUPID、KINDID、VALIDATIONID组合存在一条DATAREPORTID不同的记录；
• 停用触发器后插入，出现主键约束PK_STAT_VALIDATION冲突错误，提示复合主键已存在。

需要解释：为何触发器会触发？为何出现主键冲突？明明手动查询无重复记录，这一现象怎么解释？

1. 主键索引损坏或数据不一致 #

Firebird的主键约束依赖唯一索引实现，当索引因磁盘IO错误、服务器突然断电等原因损坏时，会出现索引记录与实际表数据不匹配的情况：

• 索引中存在该复合主键的条目，但实际表中没有对应数据，所以手动查询表找不到记录；
• 触发器的EXISTS查询会优先使用主键索引，索引里的错误条目让触发器判定记录已存在；
• 插入时主键约束检查同样依赖索引，因此会抛出冲突错误。

2. 未提交事务的影响 #

如果存在未提交的事务，该事务可能已经插入了该主键组合的记录：

• 手动查询时因事务隔离级别（比如SNAPSHOT）无法看到未提交的数据，但触发器和主键约束检查会感知到这条未提交的记录；
• 若重启数据库后问题依旧，即可排除该原因（未提交事务会随数据库重启自动回滚）。

3. 数据类型隐式转换异常 #

注意到DATAREPORTID是BIGINT类型，PARAMGROUPID是INTEGER类型，若应用端传入参数时出现隐式类型转换错误：

• 比如传入的数值超出INTEGER范围被截断，或字符串转数值时出现异常，导致实际插入的主键值和预期不一致；
• 这种情况下，实际插入的主键组合可能在索引中已存在，但你查询的是预期值，所以找不到对应记录。

• 重建主键索引：先备份数据，再删除并重新添加主键约束，强制Firebird重建索引：

  ALTER TABLE STAT_VALIDATION DROP CONSTRAINT PK_STAT_VALIDATION;
  ALTER TABLE STAT_VALIDATION ADD CONSTRAINT PK_STAT_VALIDATION PRIMARY KEY (DATAREPORTID, PARAMGROUPID, KINDID, VALIDATIONID);
  

• 检查数据库完整性：使用Firebird的gfix工具检查并修复数据库：

  gfix -v -full -user sysdba -password your_db_password /path/to/your/database.fdb
  

• 排查活跃事务：查询数据库中的未提交事务，清理长时间挂起的事务：

  SELECT * FROM MON$TRANSACTIONS;
  

• 验证插入参数准确性：开启Firebird的SQL日志，确认应用实际传入的参数值是否与预期一致。

内容的提问来源于stack exchange，提问作者Andriy Semenets