foreign keyの設定で「 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails」のエラーが出たときの対処法

MySQLで既存のテーブルに後で外部キー制約を付けようとした所、下記のエラーが出た。

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

今回は、このエラーの解決法を紹介していく。

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint failsとは？

上記は「child rowが追加または更新ができないから、外部キー制約が失敗したよ」と言うエラー。

例えば、下記の様なusersテーブルがあるとしよう。

id PRIMARY KEY
name VARCHAR(255)
password VARCHAR(255)

既にusersテーブルには10件のUserが保存されているとしよう。

そこで下記のSQLを実行して、新たに外部キー制約を付けることにした。

ALTER TABLE users
ADD twitter_id BIGINT(11) NOT NULL,
ADD CONSTRAINT FOREIGN KEY (twitter_id) REFERENCES twitters(id);

この時に上記のエラーが発生する。

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint failsの原因とは何か？

外部キー制約とは「外部キーとなるカラムに入る値は、参照するテーブルのカラムに存在する値しか入れてはいけない」と言う制約を課すものである。

つまり、twitter_idカラムに入る値は、twittersテーブルのidカラムに存在する値しか入れてはいけなくなる。 (MySQLは例外的にNULLを許容している。)

しかし、上記のSQLでは、ADD twitter_id BIGINT(11) NOT NULL,となっているので、NULLを代入することはできないし、 twittersテーブルに紐づいた値も入っていないので、上記のエラーが発生した。

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint failsの解決法

一番良いのは、外部キー制約をする前にtwitter_idカラムに適切な値を入れるようにすること。

もし、開発環境で気軽に壊せる場合は、twitter_idカラムのNULL許容にするのも良い。