CODEWITHSUNDEEP
mysqlforeign-keys
Brotzka
Brotzka

Reputation: 2793

Adding foreign key constraint results in Integrity constraint violation: 1452 Cannot add or update a child row during

The following error occurs when I am trying to add a foreign key constraint to one of my tables running the following command:

alter table `word_lists` add constraint `word_lists_image_id_foreign` foreign key (`image_id`) references `images` (`id`) on delete set null;

The complete error message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (palabi_54.#sql-3a2_395, CONSTRAINT word_lists_image_id_foreign FOREIGN KEY (image_id) REFERENCES images (id) ON DELETE SET NULL) (SQL: alter table word_lists add constraint word_lists_image_id_foreign foreign key (image_id) references images (id) on delete set null)

This is my word_lists table: word_lists table

And this is my images table: images table

Upvotes: 1

Views: 1960

Answers (1)

Brotzka
Brotzka

Reputation: 2793

So I found the solution by myself:

The word_lists table already had records stored in it. These records had 0 instead of NULL as value for image_id. Also I had to set the default value for image_id in word_lists to NULL. And this field has to be nullable.

Upvotes: 1

Related Questions