mysql - Laravel 迁移 - 违反完整性约束 : 1452 Cannot add or update a child row: a foreign key constraint fails

Question

我正在尝试为我通过此迁移创建的表 inventories 运行迁移:

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();
});

我正在尝试添加一个运行迁移，我正在向表中添加一个外键:

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

但是，当我尝试运行迁移时出现错误:

[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 )

我做错了什么？

Answer 1

我遇到了同样的问题。通过将 nullable 添加到字段来修复它:

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

请注意，迁移后所有现有行都将具有 some_id = NULL。

更新:

从 Laravel 7 开始，有更多更快捷的方式来做同样的事情:

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

nullable 在 constrained 之前也很重要。

您可以在此处的 official documentation 中找到更多信息