PostgreSQL删除trip表报外键约束错误但otheritems无关联数据问题求解

• CTE执行机制与约束检查时序问题：PostgreSQL中所有CTE子句的修改操作都基于SQL语句启动时的数据库快照执行，且外键一致性检查默认延迟到整个SQL语句全部执行完成后触发。你当前的SQL顺序是先删除主表trip的记录，再删除关联从表记录，执行时先标记trip记录为删除，后续从表删除操作的结果不会被前置的外键检查逻辑感知，因此会触发引用约束报错。
• 外键字段名匹配问题：报错明确外键关联的是otheritems表的trip_id字段，你单独查询时用的过滤条件是tripid，如果两个字段名称不统一，会出现你以为无关联数据、实际存在关联的情况，建议先核对字段名拼写是否正确。

调整删除顺序，先查询待删除的目标trip_id，再逐层删除所有关联从表数据，最后删除主表trip的记录即可，参考SQL如下：

WITH target_trips AS (
    -- 第一步仅查询待删除的trip_id，不提前操作主表
    SELECT trip_id FROM trip WHERE end_date < NOW() - INTERVAL '10.6 years'
),
deleted_otheritems AS (
    DELETE FROM otheritems WHERE trip_id IN (SELECT trip_id FROM target_trips)
    RETURNING otheritemid
),
deleted_constraintviolation AS (
    DELETE FROM constraintviolation WHERE trip_id IN (SELECT trip_id FROM target_trips)
),
deleted_currencyprovisioning AS (
    DELETE FROM currencyprovisioning WHERE trip_id IN (SELECT trip_id FROM target_trips)
),
deleted_trip_approver_snapshot AS (
    DELETE FROM trip_approver_snapshot WHERE trip_id IN (SELECT trip_id FROM target_trips)
),
deleted_requestforconsultantassistance AS (
    DELETE FROM requestforconsultantassistance WHERE trip_id IN (SELECT trip_id FROM target_trips)
),
deleted_caritem_other AS (
    DELETE FROM caritemdetail_otheritems WHERE otheritemid IN (SELECT otheritemid FROM deleted_otheritems)
)
-- 所有关联从表数据清理完成后，最后删除主表记录
DELETE FROM trip WHERE trip_id IN (SELECT trip_id FROM target_trips);

如果核对后确认otheritems表关联trip的字段确实为tripid，可执行\d otheritems命令查看表的外键约束定义，确认约束关联的字段是否符合预期。

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