【MySQL】外部キー制約を満たしていないレコードがあるかチェックするプロシージャ

　やんごとなき理由で外部キー制約を外した状態のデータベースを操作する場合があります。また外部キー制約がなかったところに外部キー制約を追加することがあります。そういった作業をした後は外部キー制約が正しく守られているかチェックしたくなります。このチェックはチェック対象が少ない――例えば『投稿の投稿者アカウント』に関する外部キー制約のみを調査するという話なら簡単です。これだけなら次のSQLを実行して結果件数を見るだけでOKです。

SELECT
  COUNT(*) AS 対応する投稿者アカウントが見つからなかった投稿数
FROM posts
-- 目的の外部キー制約でLEFT JOINすることにより、author_id カラムに値はあるが対応する users.user_id が存在しない行を取得対象に含められる
LEFT JOIN users ON posts.author_id = users.user_id
WHERE
    posts.author_id IS NOT NULL  -- 投稿者アカウントIDがNULLの投稿は調査対象外
  AND users.user_id IS NULL      -- 対応する投稿者アカウントが存在しない

　調査対象が少なければ人力でこれを改造したSQLを複数回実行するだけでいいのですが、とても人力でやっていられないぐらい対象が多い時もあります。そういった時は外部キー制約情報を基にSQLを組み立て、そのSQLを実行する、というストアドプロシージャを使った方がいいです。このストアドプロシージャは例えば次のように作れます。

　コメントに出てくる親は参照先、子は参照元、孤児は参照先を持たない参照元です。先ほどの投稿の例でいえば、親が users、子が posts、孤児が posts.author_id に対応する users.user_id が存在しない posts のレコードです。

DELIMITER $$

# 作り直す時用の必要な時だけ削除処理
DROP PROCEDURE IF EXISTS check_fk_integrity $$
# 本体
CREATE PROCEDURE check_fk_integrity(IN in_schema VARCHAR(64))
BEGIN
    -- =====================================================
    -- ① 変数・ハンドラ・カーソル定義
    -- =====================================================

    -- ループ終了検知用フラグ
    DECLARE done INT DEFAULT 0;

    -- カーソルから受け取る各種メタ情報（制約・子表・親表）
    DECLARE v_constraint_schema VARCHAR(64);   -- 制約が属するスキーマ
    DECLARE v_constraint_name   VARCHAR(128);  -- 制約名（FK名）
    DECLARE v_child_schema      VARCHAR(64);   -- 子テーブルのスキーマ
    DECLARE v_child_table       VARCHAR(64);   -- 子テーブル名
    DECLARE v_parent_schema     VARCHAR(64);   -- 親テーブルのスキーマ
    DECLARE v_parent_table      VARCHAR(64);   -- 親テーブル名

    -- -----------------------------------------------------
    -- スキーマ内の全FK（制約単位）を列挙するカーソル
    --   - REFERENTIAL_CONSTRAINTS: FK制約の親情報
    --   - KEY_COLUMN_USAGE: FKを構成する列対応（子列→親列）
    -- GROUP BYで「制約単位」にまとめる
    -- -----------------------------------------------------
    DECLARE fk_cur CURSOR FOR
        SELECT
            rc.CONSTRAINT_SCHEMA,
            rc.CONSTRAINT_NAME,
            kcu.TABLE_SCHEMA            AS child_schema,
            kcu.TABLE_NAME              AS child_table,
            kcu.REFERENCED_TABLE_SCHEMA AS parent_schema,
            kcu.REFERENCED_TABLE_NAME   AS parent_table
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
          ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
         AND rc.CONSTRAINT_NAME   = kcu.CONSTRAINT_NAME
        WHERE rc.CONSTRAINT_SCHEMA = in_schema
        GROUP BY
            rc.CONSTRAINT_SCHEMA, rc.CONSTRAINT_NAME,
            child_schema, child_table, parent_schema, parent_table
        ORDER BY child_table, rc.CONSTRAINT_NAME;

    -- カーソルFETCHで結果が枯渇したらdone=1にするハンドラ
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- =====================================================
    -- ② 準備処理（セッション設定・一時テーブル作成）
    -- =====================================================

    -- GROUP_CONCATの最大長を拡張（複数列FKでも長い式を安全に組めるように）
    SET @@SESSION.group_concat_max_len = 1024 * 1024;

    -- 結果を貯める一時テーブル（TEXTを扱うのでInnoDBのテンポラリを明示）
    CREATE TEMPORARY TABLE IF NOT EXISTS fk_bad_only
    (
        constraint_schema VARCHAR(64),   -- 制約スキーマ
        constraint_name   VARCHAR(128),  -- 制約名（FK）
        child_schema      VARCHAR(64),   -- 子スキーマ
        child_table       VARCHAR(64),   -- 子テーブル
        parent_schema     VARCHAR(64),   -- 親スキーマ
        parent_table      VARCHAR(64),   -- 親テーブル
        orphans           BIGINT,        -- 孤児件数
        example_sql       TEXT           -- 孤児抽出の具体的なSQL（サンプル）
    ) ENGINE = InnoDB;

    -- =====================================================
    -- ③ 全FKを走査して孤児を数えるメインループ
    -- =====================================================

    OPEN fk_cur;

    fk_loop:
    LOOP
        -- 1件分のFKメタを取得
        FETCH fk_cur INTO
            v_constraint_schema, v_constraint_name,
            v_child_schema,      v_child_table,
            v_parent_schema,     v_parent_table;

        -- 終了判定
        IF done = 1 THEN
            LEAVE fk_loop;
        END IF;

        -- -------------------------------------------------
        -- ③-1 子↔親の列対応から3種類の条件文字列を組み立てる
        --     a) JOIN条件   : c.child_col = p.parent_col AND ...
        --     b) 子の非NULL : c.child_col IS NOT NULL AND ...
        --     c) 親キー先頭: 親側先頭列名（NULLチェック対象カラムの決定用）
        --   ※複合FKを想定しPOSITION_IN_UNIQUE_CONSTRAINT順で整列
        -- -------------------------------------------------
        SET @join_cond        := NULL;
        SET @nonnull_cond     := NULL;
        SET @parent_first_id  := NULL;

        SELECT
            -- JOIN条件（子列=親列）をAND連結
            GROUP_CONCAT(
                CONCAT('c.`', kcu.COLUMN_NAME, '` = p.`', kcu.REFERENCED_COLUMN_NAME, '`')
                ORDER BY kcu.POSITION_IN_UNIQUE_CONSTRAINT
                SEPARATOR ' AND '
            ) AS join_cond,

            -- 子列の非NULL条件をAND連結（NULLはFK未設定扱いなので除外）
            GROUP_CONCAT(
                CONCAT('c.`', kcu.COLUMN_NAME, '` IS NOT NULL')
                ORDER BY kcu.POSITION_IN_UNIQUE_CONSTRAINT
                SEPARATOR ' AND '
            ) AS nonnull_cond,

            -- 親側の先頭カラム名（LEFT JOIN後のNULL判定に使う）
            SUBSTRING_INDEX(
                GROUP_CONCAT(CONCAT('`', kcu.REFERENCED_COLUMN_NAME, '`')
                             ORDER BY kcu.POSITION_IN_UNIQUE_CONSTRAINT SEPARATOR ','),
                ',', 1
            ) AS parent_first_id
        INTO @join_cond, @nonnull_cond, @parent_first_id
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        WHERE kcu.CONSTRAINT_SCHEMA = v_constraint_schema
          AND kcu.CONSTRAINT_NAME   = v_constraint_name
          AND kcu.TABLE_SCHEMA      = v_child_schema
          AND kcu.TABLE_NAME        = v_child_table;

        -- 列対応がうまく取れない（理論上ありえないが安全側）場合は次へ
        IF @join_cond IS NULL OR @nonnull_cond IS NULL OR @parent_first_id IS NULL THEN
            ITERATE fk_loop;
        END IF;

        -- -------------------------------------------------
        -- ③-2 孤児件数の算出（動的SQL）
        --   ・子←親をLEFT JOINし、親側の先頭キーがNULLの行が「孤児」
        --   ・子キーがNULLの行はFK未設定なので除外（@nonnull_cond）
        --   ・EXECUTE ... INTOは不可のため、SQL内部でINTO @cntを記述
        -- -------------------------------------------------
        SET @cnt := 0;

        SET @count_sql = CONCAT(
            'SELECT COUNT(*) INTO @cnt ',
            'FROM `', v_child_schema,  '`.`', v_child_table,  '` c ',
            'LEFT JOIN `', v_parent_schema, '`.`', v_parent_table, '` p ',
            'ON (', @join_cond, ') ',
            'WHERE p.', @parent_first_id, ' IS NULL ',
            'AND (', @nonnull_cond, ')'
        );

        PREPARE s1 FROM @count_sql;
        EXECUTE s1;           -- ← 動的SQL内で「INTO @cnt」済み
        DEALLOCATE PREPARE s1;

        -- -------------------------------------------------
        -- ③-3 孤児が存在する場合のみ、結果テーブルに積む
        --       併せて100件だけ中身を確認できるサンプルSQLも作る
        -- -------------------------------------------------
        IF COALESCE(@cnt, 0) > 0 THEN
            SET @example_sql = CONCAT(
                'SELECT c.* ',
                'FROM `', v_child_schema,  '`.`', v_child_table,  '` c ',
                'LEFT JOIN `', v_parent_schema, '`.`', v_parent_table, '` p ',
                'ON (', @join_cond, ') ',
                'WHERE p.', @parent_first_id, ' IS NULL ',
                'AND (', @nonnull_cond, ') ',
                'LIMIT 100;'
            );

            INSERT INTO fk_bad_only
                (constraint_schema, constraint_name,
                 child_schema, child_table,
                 parent_schema, parent_table,
                 orphans, example_sql)
            VALUES
                (v_constraint_schema, v_constraint_name,
                 v_child_schema, v_child_table,
                 v_parent_schema, v_parent_table,
                 @cnt, @example_sql);
        END IF;

    END LOOP;

    -- カーソルを閉じる
    CLOSE fk_cur;

    -- =====================================================
    -- ④ 最終出力と後片付け
    -- =====================================================

    -- 孤児の多い順に並べて返す（次点で表・制約名）
    SELECT *
    FROM fk_bad_only
    ORDER BY orphans DESC, constraint_schema, child_table, constraint_name;

    -- 一時テーブルを破棄
    DROP TEMPORARY TABLE IF EXISTS fk_bad_only;
END $$

DELIMITER ;

-- 使用例
CALL check_fk_integrity('test_db');

　使用例の結果例が次表です。scenes というテーブルを親とする scene_attach_files テーブルに外部キー制約を満たしていないレコードがある場合の例です。example_sqlにあるSQLを実行すれば、より具体的な異常がわかります。

　こんな感じで外部キー制約が守られているか後付けでチェックできます。余談ですが、上記ストアドの作成はかなりLLMに頼ってます。コンテキストを工夫しなくとも十全に指示できるような作業は任せやすいですね。