外部キーを張ったテーブルのUNIQUEインデックスを削除する手順

同じ問題に答えを2回登録できなくするために UNIQUE を張ったけれど、

同じ問題のIDを入れることを忘れて張ってしまったことに、データを入れてから気が付いた。

あ、やってしまった。

しかも外部キーも入れてしまっているので処理が大変なテーブルだ。

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  test_id INT NOT NULL,
  test_text VARCHAR(255) NOT NULL,

  UNIQUE (test_id, test_text),

  CONSTRAINT fk_test
    FOREIGN KEY (test_id)
    REFERENCES questions(id)
    ON DELETE CASCADE
);

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  test_id INT NOT NULL,
  test_text VARCHAR(255) NOT NULL,

  UNIQUE (test_id, test_text),

  CONSTRAINT fk_test
    FOREIGN KEY (test_id)
    REFERENCES questions(id)
    ON DELETE CASCADE
);

その時に UNIQUE を削除したので手順をしたためておきます。

必ずDBのバックアップを取ってから行ってくださいね！

外部キーとUNIQUEインデックスを確認

下記のSQLをPhpMyAdminに叩きます。

SHOW CREATE TABLE test;

SHOW CREATE TABLE test;

上記のような画面が出てきます。

でも、Create Tableが少ししか表示されないので赤枠で囲った「拡張オプション」をクリックします。

オプションがたくさん出てくるのでデフォルトでは「部分テキスト」になっているので、

これを「全文」に変更し、実行ボタンを押下します。

UNIQUEインデックス ＝ UNIQUE KEY ‘〇〇〇’

外部キー＝CONSTRAINT ‘△△△’

なので、上記で言うとUNIQUEインデックスはtest_id、外部キーはfk_testになります。

これを必ずメモしておいてください。

外部キーを削除

外部キーが張られたままUNIQUEインデックスは削除できないので、外部キーを削除します。

ALTER TABLE test
DROP FOREIGN KEY fk_test;

ALTER TABLE test
DROP FOREIGN KEY fk_test;

UNIQUEインデックスを削除

お待ちかねのUNIQUEインデックスを削除します。

これで一応重複したデータが入れられます。

ALTER TABLE test
DROP INDEX test_id;

ALTER TABLE test
DROP INDEX test_id;

外部キーを再び張る

外部キーが外れたままなので、もう一度張り直します。

ALTER TABLE test
ADD CONSTRAINT fk_test
FOREIGN KEY (test_id)
REFERENCES questions(id)
ON DELETE CASCADE;

ALTER TABLE test
ADD CONSTRAINT fk_test
FOREIGN KEY (test_id)
REFERENCES questions(id)
ON DELETE CASCADE;

おわりに

今回は外部キーを張ったテーブルのUNIQUEインデックスを削除する手順をご紹介しました。

何度も言いますが、必ずDBのバックアップを取ってから実行してください！

もう本当に、外部キー制約は面倒だけれど頑張らねば。