Firebird数据库向存在主外键关联的两张表插入数据报外键错误如何解决

你遇到的报错是循环外键约束校验冲突导致的：你的CUSTOMER表ADDRESS_ID字段关联了CUSTOMER_ADDRESS表的主键ID，同时CUSTOMER_ADDRESS表的CUSTOMER_ID字段又关联了CUSTOMER表的主键ID。你在执行插入时先写了CUSTOMER表的插入语句，此时对应的CUSTOMER_ADDRESS记录还未创建，外键校验时找不到关联的目标记录，因此触发异常。
另外注意你提供的DDL存在笔误，地址表的建表语句写的是CREATE TABLE R3_CONTACT_ADDRESSES，实际关联用的表名是CUSTOMER_ADDRESS，需要先确认表名一致避免额外报错。

不需要临时禁用外键，推荐以下两种更安全的实现方式：

方案1：调整插入逻辑（无需修改表结构，最稳妥） #

先插入CUSTOMER时将ADDRESS_ID暂时设为null，等CUSTOMER_ADDRESS插入完成后，再回写更新CUSTOMER的ADDRESS_ID字段，示例SQL如下：

EXECUTE BLOCK AS
declare variable id_customer bigint;
declare variable id_address bigint;
BEGIN
-- 预生成两个主键ID
select gen_id(CUSTOMER_ID_GEN, 1) from rdb$database into :id_customer;
select gen_id(CUSTOMER_ADDRESSES_ID_GEN, 1) from rdb$database into :id_address;

-- 先插入客户表，地址ID暂时留空
INSERT INTO CUSTOMER (Id, Name, AddressId) values (:id_customer, 'Name', null);
-- 插入地址表，此时客户ID已存在，外键校验通过
INSERT INTO CUSTOMER_ADDRESS (Id, Address, Default, CustomerId) values (:id_address, 'Short Street 1', 1, :id_customer);
-- 回写客户表的地址ID，此时地址记录已存在，外键校验通过
UPDATE CUSTOMER SET AddressId = :id_address WHERE Id = :id_customer;
END

方案2：设置外键延迟校验（无需调整原有插入逻辑） #

Firebird支持将外键约束设置为事务提交时再校验，而非单条SQL执行时立即校验，修改外键定义的SQL如下：

-- 修改CUSTOMER表的外键为延迟校验
ALTER TABLE CUSTOMER DROP CONSTRAINT CUSTOMER_ADDRESS_ID_FK;
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_ADDRESS_ID_FK 
  FOREIGN KEY (ADDRESS_ID) REFERENCES CUSTOMER_ADDRESS (ID) 
  ON DELETE SET DEFAULT
  DEFERRABLE INITIALLY DEFERRED;

-- 修改CUSTOMER_ADDRESS表的外键为延迟校验
ALTER TABLE CUSTOMER_ADDRESS DROP CONSTRAINT CUSTOMER_ADDR_CUSTOMER_ID_FK;
ALTER TABLE CUSTOMER_ADDRESS ADD CONSTRAINT CUSTOMER_ADDR_CUSTOMER_ID_FK 
  FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID) 
  ON UPDATE CASCADE ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED;

修改完成后，你原本编写的插入SQL就可以直接正常运行。

不建议临时禁用外键来解决该问题，禁用外键期间如果有其他写入操作很容易产生不符合约束的脏数据，后续排查成本很高。

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