多対多テーブルにUNIQUEインデックスを作りたいのに既に重複してしまっていて作れないときの対処法

交差テーブル

きっかけ

頻繁に利用するマッピングテーブルにUNIQUEインデックス貼ることで速くなる箇所がありました。

ただ、問題が発生。

既存のレコードに重複レコードが存在していたのでUNIQUEにできない！！

UNIQUEインデックスを作るには、すべてのレコードがUNIQUEである必要があります。

ということで、重複レコードのみを消したいというわけです。

SQLコード

DELETE FROM cross_reference_table WHERE id IN 
    (SELECT id FROM 
        (SELECT crt1.id AS id FROM cross_reference_table crt1
            INNER JOIN 
                (SELECT reference1_id, reference2_id FROM cross_reference_table GROUP BY reference1_id, reference2_id HAVING COUNT(*) >= 2) AS tmp
            ON crt1.reference1_id = tmp.reference1_id
            AND crt1.reference2_id = tmp.reference2_id
            GROUP BY crt1.reference1_id, crt1.reference2_id
        ) AS t
    );

サブクエリでテーブルからGROUP BYによって選択されたreference1_id, reference2_idを二つ以上持つ（重複している）を呼び出す。
その結果を元テーブルとINNER JOIN。
該当するidを引っ張ってきて、DELETE FROM cross_reference_table WHERE id INで削除。

２階層目の(SELECT id FROM .. ) AS t は冗長ですが、副問い合わせの特徴でそうしないとあかんようです。

仕組みとしては、GROUP BYで重複レコードのみを取得しているので、クエリ結果がQuery OK, 0 row affectedになるまで打ち続ければ、重複レコードが消えて、二つのカラムの組み合わせが一意になります。

UNIQUEインデックスが貼れるようになりました！！

おまけ

交差テーブル、関連テーブル、結合テーブル、多対多テーブル、マッピングテーブル、中間テーブル。。。

あえて統一せずに書いたけど、呼称がごまんとある状態はよろしくないよなぁ。