Laravel migration - Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Question

I am trying to run a migration for a table inventories that I have created with this migration:

Schema::create('inventories', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('remote_id')->unsigned();
    $table->integer('local_id')->unsigned();
    $table->string('local_type');
    $table->string('url')->nullable()->unique();
    $table->timestamps();
});

I am trying to add a run a migration where I am adding a foreign key to the table:

Schema::table('inventories', function (Blueprint $table) {
    $table->foreign('local_id')->references('id')->on('contents')->onDelete('cascade');
});

But, I am getting an error when I try to run the migration:

[Illuminate\Database\QueryException]

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (middleton
.#sql-5d6_162a, CONSTRAINT inventories_local_id_foreign FOREIGN KEY (local_id) REFERENCES contents (id) ON DELETE CASCADE ) (SQL: alter table inventories add constraint inventories_local_id_foreign foreign key (local_id) references contents (id) on delete cascade)

[PDOException]

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (middleton
.#sql-5d6_162a, CONSTRAINT inventories_local_id_foreign FOREIGN KEY (local_id) REFERENCES contents (id) ON DELETE CASCADE )

What am I doing wrong?

Roman Meyer · Answer 1 · 2021-03-17T22:08:16.833

22

I had the same problem. Fixed it by adding nullable to field:

Schema::create('table_name', function (Blueprint $table) {
    ...
    $table->integer('some_id')->unsigned()->nullable();
    $table->foreign('some_id')->references('id')->on('other_table');
    ...
});

Note that after migration all existed rows will have some_id = NULL.

UPD:

Since Laravel 7 there is more short way to do the same thing:

$table->foreignId('some_id')->nullable()->constrained();

It is also very important that nullable goes BEFORE constrained.

More info you can find here, in official documentation

edited Mar 17 '21 at 22:08

answered Jul 23 '18 at 08:00

Roman Meyer

2,634
2
20
27

1

This is important to remember for existing databases, thanks for sparking my memory! – Andrew Fox Jan 14 '21 at 00:13
This error occurs because SQL expects the foreign id column to have data, but since we didn't mention that the field can be nullable, it throws the error. Thanks for this. – Leutecia Jan 14 '23 at 21:27

score 13 · Accepted Answer · answered Nov 13 '17 at 14:08

You probably have some records in the inventories table with local_id that does not have corresponding id in the contents table, hence the error. You could solve it by one of the two ways:

Run the migration with foreign_key_checks turned off. This will disabled the foreign key constraints for the existing rows (if that's what you want). It's documented here
Insert only those records that have corresponding id field in contents table. You can use INSERT INTO.. WHERE EXISTS query to filter the records out, and insert only those records.

Yes, once I deleted all tables and run all migrations at once, then it worked. Thanks — Leff, Nov 13 '17 at 14:51

Laravel migration - Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

2 Answers2

Linked