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?

Answer 1

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

Answer 2

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.