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
, CONSTRAINTinventories_local_id_foreign
FOREIGN KEY (local_id
) REFERENCEScontents
(id
) ON DELETE CASCADE ) (SQL: alter tableinventories
add constraintinventories_local_id_foreign
foreign key (local_id
) referencescontents
(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
, CONSTRAINTinventories_local_id_foreign
FOREIGN KEY (local_id
) REFERENCEScontents
(id
) ON DELETE CASCADE )
What am I doing wrong?
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