mysqlで擬似materialized_viewを作ろうとした時のDuplicate entry '207453' for key 'xxx.PRIMARY' の解決例

何があった？

こちらの記事に従って、mysqlで擬似materialized_viewを作り、triggerの設定（下記sql）後にinsertを試したところ出現しました。

エラー文を調査すると、auto_incrementの値がおかしい、または設定がされていないことが原因という記事がヒットしました。

しかし今回新しく作成したテーブルは全てのカラムにコピーしてきた値を入れる想定しており、primary_keyからauto_incrementを意図的に外していたので、題名のエラー文が出る意味がわかリませんでした。

とりあえず指示通りauto_incrementを設定して値も揃えてみましたが同じエラーが出続けました。

失敗したsql

DELIMITER //
CREATE TRIGGER test_table_insert
AFTER INSERT ON organizations FOR EACH ROW
BEGIN
  INSERT INTO test_table (
    organization_id,
    organization_name,
    prefectures.id,
    prefectures.name
  )
SELECT  -- ←prefecturesテーブルをjoinして一緒にinsertしたいので、selectを使ってinsertしている
  NEW.id,
  NEW.name,
  prefectures.id,
  prefectures.name
FROM
  organizations
  left join prefectures on prefectures.id = organizations.prefecture_id
where
  organizations.deleted_at is null  
order by
  organizations.id;
END;
//
DELIMITER ;

結論（原因）

triggerに使ったselect文が間違っていました。

具体的には新しくinsertする値をselectで指定する場合はwhere区でorganizations.id = NEW.idと指定しなければならなかったのに気づかず、その記述なしで設定していました。

結果としてNEW.id以外のidがinsertで重複してしまっていたのですが、一番idの大きいNEW.idがエラーの代表idとして重複のエラー文に表示されていました。

解決方法

triggerでselectを使ってinsertする場合は、where区にorganizations.id = NEW.idを指定することで解決できました。

成功したsql

DELIMITER //
CREATE TRIGGER test_table_insert
AFTER INSERT ON organizations FOR EACH ROW
BEGIN
  INSERT INTO test_table (
    organization_id,
    organization_name,
    prefectures.id,
    prefectures.name
  )
SELECT
  NEW.id,
  NEW.name,
  prefectures.id,
  prefectures.name
FROM
  organizations
  left join prefectures on prefectures.id = organization.prefecture_id
where
  organizations.deleted_at is null
  AND organizations.id = NEW.`id`  -- ←ここを追加したらエラーが解消された
order by
  organizations.id;
END;
//
DELIMITER ;