As we are actively developing projects and database tables are updated often and new ones are added we tend to run into all kinds of database related errors. And one of the common ones we get is “Cannot add or update a child row: a foreign key constraint fails”

Integrity Constraint Violation

The query exception you could get running php artisan migrate using a migration where you are adding a foreign key when you do not declare the table it is referring to is:

[Illuminate\Database\QueryException]
 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`dbname`.`#sql-56d_44c`, CONSTRAINT `projects_standard_cheese_foreign` FOREIGN KEY (`standard_cheese`) REFERENCES `bacon` (`id`)) (SQL: alter table `projects` add constraint `projects_standard_cheese_foreign` foreign key (`standard_cheese`) references
 `cheeses` (`id`))

There always also is a PDO Exception added to the error report as well:

[PDOException]
 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`dbname`.`#sql-56d_44c`, CONSTRAINT `projects_standard_cheese_foreign` FOREIGN KEY (`standard_cheese`) REFERENCES `cheeses` (`id`))

It basically tells you the same, but using a more specific SQL error.

Migration Culprit

In this case a foreign key is added with a migration to alter the projects table:

$table->foreign('standard_cheese')->references('id')->on('cheeses');

The foreign key is as always added to make a connection or a relationship between tables. When you have a clean database that is not an issue, but when you already have tables and content it is.

Table Content Blockade

One of the issues is that the table already has data. And it is data I prefer to keep for testing purposes.  But this data misses this connection as it did not exist before the migration. If you remove the data and run the migration you will see it will work just fine. There will then be no constraints. But when you want to re-add the same data again you will get the same issues. This as this is the old data that missed the connection.

Turn off Foreign Key Check

What you can do to avoid this error is use:

SET FOREIGN_KEY_CHECKS = 0;

This you use to turn off the foreign key check-up. This is OK when you are just building your database structure and Laravel app. This is not OK when you have one in production already of course.

When you do this you can run the SQL Query and re-add the existing rows for both the cheeses table and the table you were adding the foreign key to. Do not forget to run a query to turn this check back on. You can use:

SET FOREIGN_KEY_CHECKS = 1;

Basic Issue

The basic issue is that “you need to have already declared the table that the foreign key references, before you can define a foreign key that references it.” as Michael a DBA Pro mentioned here at DBA Stackexchange. So when you create migrations to adjust tables and add new foreign keys that is something you should think about. You could work with dummy data to seed the database again, but when you make significant changes you may have to do it all by hand in the end.