Laravel迁移-违反完整性约束:1452无法添加或更新子行:外键约束失败 [英] Laravel migration - Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

问题描述

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

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]

[Illuminate\Database\QueryException]

SQLSTATE [23000]:违反完整性约束:1452无法添加或 更新子行:外键约束失败(middleton
.#sql-5d6_162a，约束inventories_local_id_foreign外部 删除级联上的键(local_id)参考contents(id)) (SQL:更改表inventories添加约束 inventories_local_id_foreign外键(local_id)引用 contents(id)在删除级联上)

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]

[PDOException]

SQLSTATE [23000]:违反完整性约束:1452无法添加或 更新子行:外键约束失败(middleton
.#sql-5d6_162a，约束inventories_local_id_foreign外部 键(local_id)参考contents(id)在删除级联上)

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 )

我在做什么错了?

推荐答案

您可能在inventories表中有一些记录，其中local_id在contents表中没有对应的id，因此会出现错误.您可以通过以下两种方法之一解决它:

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:

• 在关闭foreign_key_checks的情况下运行迁移.这将禁用现有行的外键约束(如果这就是您想要的). 此处
• 仅插入在contents表中具有对应的id字段的那些记录.您可以使用INSERT INTO.. WHERE EXISTS查询将记录过滤掉，并仅插入那些记录.

• 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.

这篇关于Laravel迁移-违反完整性约束:1452无法添加或更新子行:外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！