不能添加外键约束- Laravel 9

Question

我有posts表:

Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('imid');
$table->string('name');
$table->text('body');
$table->timestamps();
});

和

图像表:

Schema::create('images', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('imid');
$table->string('name')->nullable();
$table->longText('image_path')->nullable();
$table->timestamps();
});

我试图添加一个外部字段到现有的帖子表在一个单独的迁移:

public function up()
{
Schema::table('posts', function (Blueprint $table) {
$table->foreign('imid')->references('imid')->on('images')->onDelete('cascade');
});
}

public function down()
{
Schema::table('posts', function (Blueprint $table) {
$table->dropColumn('imid');
});
}

但是当我运行

php artisan migrate

I get error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `posts` add constraint `posts_imid_foreign` foreign key (`imid`) references `images` (`imid`) on delete cascade)

似乎我做得对。我遗漏了什么?

Answer 1

1. 检查您的两个表都有创建外键(imid)的列，并且这些列具有相同的类型。您可以使用以下命令来执行此操作:

DESCRIBE posts;
DESCRIBE images;

2. 接下来，检查posts表中是否没有指向images表中不存在记录的imid值(如果有-将它们连接到现有记录或删除它们)

SELECT posts.id, posts.imid FROM posts
LEFT JOIN images ON posts.imid = images.imid
WHERE images.id IS NULL;

3. 确保你的表使用的是InnoDB引擎

SHOW TABLE STATUS WHERE name = 'posts';

如果Engine != InnoDB，则运行:

ALTER TABLE posts ENGINE = InnoDB;

最后，我建议您更改迁移的down方法，以便它与up方法相反:

Schema::table('posts', function (Blueprint $table) {
$table->dropForeign('posts_imid_foreign');
});