外部キー制約にCASCADEを追加

作成したテーブルに外部キー制約を追加するためにまず対象のカラムの外部キー制約を削除する必要があるらしい。
ここにたどり着くまで時間がかかったので自分用にメモ。

やりたいこと

threadテーブルのidを外部キーとして参照しているcommentsテーブルのthreadIdに、thread.idが削除されたらcommentsのレコードも削除されるように設定したい。

テーブルの構造、外部キーの名前を確認

外部キーの名前は”comments_ibfk_1”と"comments_ibfk_2"であることがわかる。

mysql> show create table comments;
+----------+--------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
+----------+--------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `threadId` int(11) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `body` varchar(700) COLLATE utf8mb4_bin DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `fk_cmt_th_id` (`threadId`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`threadId`) REFERENCES `thread` (`id`),
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+--------------------------------------------------+
1 row in set (0.00 sec)

外部キー制約をいったん削除する

新しく外部キー制約を追加する場合、対象になっている外部キー制約を削除する必要があるらしい。
今回はthreadIdにCASCADEを追加したいので"comments_ibfk_1"の制約を削除する。

mysql> ALTER TABLE comments DROP FOREIGN KEY comments_ibfk_1;
//ALTER TABLE テーブル名 DROP FOREIGN KEY 外部キー名;

外部キーの再設定

"ON DELETE CASCADE"は、親テーブルのデータの削除に伴い、それを外部キーとしているデータも削除するという制約。"ON UPDATE CASCADE"は親テーブルのデータの更新と連携する。

mysql>ALTER TABLE comments ADD CONSTRAINT comments_ibfk_1 FOREIGN KEY (threadId) REFERENCES thread(id) ON DELETE CASCADE ON UPDATE CASCADE;
//ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY (対象のカラム名) REFERENCES 親テーブル名(参照元カラム名) ON DELETE CASCADE ON UPDATE CASCADE;

結果

threadのidを参照元としてthreadIdを設定した外部キーcomments_ibfk_1に、CASCADEの制約が追加されたことがわかる。

mysql> show create table comments;
+----------+--------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
+----------+--------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `threadId` int(11) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `body` varchar(700) COLLATE utf8mb4_bin DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `comments_ibfk_1` (`threadId`),
  KEY `userId` (`userId`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`threadId`) REFERENCES `thread` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+--------------------------------------------------+
1 row in set (0.00 sec)