保存含hasMany关联记录时触发复合唯一索引重复条目错误

现有配置 #

• ConfigurationsTable 与 ValuesTable 为 hasMany 关联
• 反向关联：
  • ValuesTable 属于 ConfigurationsTable（belongsTo）
  • ValuesTable 属于 ParametersTable（belongsTo）
• 数据库通过configuration_id和parameter_id的复合唯一索引，确保每个值对应唯一的配置-参数组合。

问题描述 #

保存关联了Values的Configuration实体时，CakePHP能识别关联并创建新记录，但触发唯一索引冲突错误：

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-2' for key 'idx_configuration_id_parameter_id_UNIQUE'

触发错误的SQL语句为：

INSERT INTO values (configuration_id, parameter_id, value) VALUES (2, 2, 'hello world 2')

保存代码如下：

if ($this->getRequest()->is('put')) {
    $entity = $this->Configurations->patchEntity($entity, $this->getRequest()->getData(), [
        'associated' => ['Values'],
    ]);
    $this->Configurations->save($entity, [
        'associated' => ['Values'],
    ]);
    return $this->redirect(['action' => 'index']);
}

需求：希望CakePHP执行save()时自动添加ON DUPLICATE KEY UPDATE value=value子句，无需手动重构插入语句。

方法1：针对关联保存配置upsert选项（推荐，CakePHP 3.6+支持） #

直接在保存Configuration时，给关联的Values配置upsert参数，指定唯一索引和更新字段：

if ($this->getRequest()->is('put')) {
    $entity = $this->Configurations->patchEntity($entity, $this->getRequest()->getData(), [
        'associated' => ['Values'],
    ]);
    $this->Configurations->save($entity, [
        'associated' => [
            'Values' => [
                'upsert' => true,
                'updateFields' => ['value'], // 冲突时需要更新的字段
                'constraint' => 'idx_configuration_id_parameter_id_UNIQUE' // 数据库中定义的复合唯一索引名
            ]
        ]
    ]);
    return $this->redirect(['action' => 'index']);
}

配置后CakePHP会自动生成带ON DUPLICATE KEY UPDATE的SQL语句，遇到唯一索引冲突时自动更新指定字段。

方法2：在ValuesTable中全局配置 #

如果所有Values的保存都需要这个逻辑，可以在ValuesTable的初始化方法里设置默认保存选项：

// src/Model/Table/ValuesTable.php
public function initialize(array $config): void
{
    parent::initialize($config);
    // 原有关联配置
    $this->belongsTo('Configurations');
    $this->belongsTo('Parameters');
    
    // 全局设置upsert规则
    $this->setSaveOptions([
        'upsert' => true,
        'updateFields' => ['value'],
        'constraint' => 'idx_configuration_id_parameter_id_UNIQUE'
    ]);
}

之后保存Configuration时，只需正常指定associated => ['Values']，就能自动触发upsert逻辑。

注意事项 #

• 确保CakePHP版本在3.6及以上，upsert功能是该版本后引入的。
• constraint参数必须和数据库中定义的复合唯一索引名完全一致。
• 若需更新多个字段，在updateFields数组中添加对应字段名即可，比如['value', 'updated_at']。

内容的提问来源于stack exchange，提问作者ᴍᴇʜᴏᴠ