Laravel 迁移 - 违反完整性约束：1452 无法添加或更新子行：外键约束失败

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

提问by Leff

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

我正在尝试为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();
});

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_foreignFOREIGN KEY (local_id) REFERENCES contents(id) ON DELETE CASCADE ) (SQL: alter table inventoriesadd constraint inventories_local_id_foreignforeign 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_foreignFOREIGN KEY (local_id) REFERENCES contents(id) ON DELETE CASCADE )

[照明\数据库\查询异常]

SQLSTATE[23000]: 完整性约束违规：1452 无法添加或更新子行：外键约束失败 ( middleton
. #sql-5d6_162a, CONSTRAINT inventories_local_id_foreignFOREIGN KEY ( local_id) REFERENCES contents( id) ON DELETE CASCADE ) (SQL: 更改表inventories添加约束 inventories_local_id_foreign外键 ( local_id) 引用 contents( id) 在删除级联上）

[PDO 异常]

SQLSTATE[23000]：完整性约束违规：1452 无法添加或更新子行：外键约束失败（middleton
. #sql-5d6_162a, CONSTRAINT inventories_local_id_foreignFOREIGN KEY ( local_id) REFERENCES contents( id) ON DELETE CASCADE ）

What am I doing wrong?

我究竟做错了什么？

采纳答案by Darshan Mehta

You probably have some records in the inventoriestable with local_idthat does not have corresponding idin the contentstable, hence the error. You could solve it by one of the two ways:

您可能在inventories表中有一些记录，但在表local_id中没有对应id的记录contents，因此出现错误。您可以通过以下两种方式之一解决：

• Run the migration with foreign_key_checksturned 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 idfield in contentstable. You can use INSERT INTO.. WHERE EXISTSquery to filter the records out, and insert only those records.

• 在foreign_key_checks关闭的情况下运行迁移。这将禁用现有行的外键约束（如果这是您想要的）。它记录在这里
• 只插入那些id在contents表中具有相应字段的记录。您可以使用INSERT INTO.. WHERE EXISTS查询来过滤记录，并仅插入这些记录。

回答by Roman Bobrik

had the same problem. fixed it by adding nullableto field

有同样的问题。通过添加nullable到字段来修复它

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

请注意，迁移后所有存在的行都将具有 some_id = NULL