如何在 PostgreSQL 中正确延迟外键约束检查-Golang-PHP中文网

如何在 PostgreSQL 中正确延迟外键约束检查

PostgreSQL 支持 DEFERRABLE INITIALLY DEFERRED 外键，但仅当所有相关 DML 操作（如 INSERT）在同一个显式事务内执行时，延迟检查才生效；若语句分散在多个独立事务或未正确复用事务对象，约束仍会立即触发报错。

postgresql 支持 `deferrable initially deferred` 外键，但仅当所有相关 dml 操作（如 insert）在**同一个显式事务内执行**时，延迟检查才生效；若语句分散在多个独立事务或未正确复用事务对象，约束仍会立即触发报错。

在你的场景中，campaign_r_company 表已正确定义为可延迟外键：

CREATE TABLE campaign_r_company (
  campaign_id    UUID         NOT NULL REFERENCES campaign(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  company_id     VARCHAR(32)  NOT NULL,
  PRIMARY KEY (campaign_id, company_id)
);

该定义本身是正确的：DEFERRABLE INITIALLY DEFERRED 表示约束将在事务提交（COMMIT）时统一校验，而非每条 INSERT 后立即检查。

⚠️ 但问题根源在于事务作用域未被正确维持。从你提供的代码可见：

d.Begin() 创建了一个事务 tx；
h(tx) 被调用，其中执行了两条关键操作：
1. 向 hp_campaign 插入主记录（生成 campaign_id）；
2. 调用 d.attach(...) 向 campaign_r_company 插入关联记录。

然而，d.attach() 方法内部使用的是 d.db.Exec(...) —— 它直接作用于原始数据库连接 d.db，而非传入的事务 tx。这意味着第二步的 INSERT 实际运行在自动提交的隐式事务中，完全脱离 tx 上下文。因此，此时外键引用的 campaign(id) 尚未在数据库中持久化（只存在于 tx 中），导致违反约束报错：

insert or update on table "campaign_r_company" violates foreign key constraint ...

✅ 解决方案：确保所有操作共享同一事务对象

修改 attach() 方法，使其接受并使用事务对象（如 *sql.Tx），而非硬编码 d.db：

func (d *Database) attach(tx *sql.Tx, entityName string, tableName string, ids []string, campaignID string) error {
    for _, id := range ids {
        stmt := fmt.Sprintf(`INSERT INTO %s (campaign_id, %s_id) VALUES ($1, $2)`, 
            tableName, entityName)
        _, err := tx.Exec(stmt, campaignID, id) // ← 关键：使用 tx，非 d.db
        if err != nil {
            return err
        }
    }
    return nil
}

同时更新 h(tx) 中的调用：

if (opts & StoreOptionStoreRelated) == StoreOptionStoreRelated {
    err := d.attach(tx, "company", "campaign_r_company", v.Companies, v.Id) // ← 传入 tx
    if err != nil {
        return err
    }
}

? 额外建议与注意事项：

始终对事务进行 defer tx.Rollback() 防御性处理（在 Commit() 前），避免异常时资源泄漏；
不要混用 *sql.DB 和 *sql.Tx 执行同一批逻辑 —— 事务内所有 SQL 必须通过 *sql.Tx 执行；
可通过 SET CONSTRAINTS ALL DEFERRED 在事务内显式设置（适用于临时调整），但表级 INITIALLY DEFERRED 已满足常规需求；
使用 pgx 等现代驱动时，注意其事务对象（如 pgx.Tx）同样不可与 pgx.Conn 混用。