CODEWITHSUNDEEP
mysqllaravellaravel-migrations
Leff
Leff

Reputation: 1298

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

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?

Upvotes: 15

Views: 16632

Answers (2)

Roman Meyer
Roman Meyer

Reputation: 2872

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

Upvotes: 23

Darshan Mehta
Darshan Mehta

Reputation: 30809

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.

Upvotes: 14

Related Questions