Edited at

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

More than 5 years have passed since last update.

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)

以上、メモでした。