外部キー貼り直し手順メモ

user テーブルと article テーブルがあり、article テーブルには user_id という user.id を参照する外部キーを持っている。 そして、article.user_id に on (delete|update) cascade を付け加えたかった

###テーブルの構造および外部キーの名前を確認する

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create table article \G
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

###外部キーを削除する

alter table article drop foreign key article_ibfk_1;
-- alter table article drop constraint article_ibfk_1; ではだめだった

###外部キーを付ける 頂いたコメントから修正

alter table article add constraint article_ibfk_1 foreign key (user_id) references user(id) on delete cascade on update cascade;
-- alter table article add foreign key (user_id) references user(id) on delete cascade on update cascade;

なお、ここで外部キーとして整合性のないデータが article テーブルに入っていると

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails …

といったエラーが出るため、そういった不整合なデータは削除するなど何らかの対処をする必要がある

###結果

mysql> show create table article \G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

以上、メモでした。

from: https://qiita.com/petitviolet/items/6e8d97b600721dcf7dc0