英文:
Laravel migrations change foreign key to be nullOnDelete() and nullable(true)
问题
我有这个迁移，第一次创建了一个外键：
Schema::table('user_plays_quizzes', function (Blueprint $table) {
$table->bigInteger('quiz_id')->unsigned()->change();
$table->foreign('quiz_id')->references('id')->on('quizzes');
});
现在我有一个新的迁移，我想将这个外键更新为nullable(true)，并且设置为nullOnDelete()。
我尝试了很多次，但总是出现错误，我觉得这是最接近的尝试：
Schema::table('user_plays_quizzes', function (Blueprint $table) {
$table->bigInteger('quiz_id')->unsigned()->nullable(true)->change();
$table->foreign('quiz_id')->references('id')->on('quizzes')->nullOnDelete();
});
可惜它也不起作用：
SQLSTATE[HY000]: General error: 1005 无法创建表
d039e62e.user_plays_quizzes(错误: 121 "写入或更新时的重复键") (SQL: 修改表user_plays_quizzes，添加外键约束user_plays_quizzes_quiz_id_foreign(quiz_id)参考quizzes(id)，删除时设置为null)
不知道如何解决它。我甚至无法删除键并重新创建它们。也许有人有办法如何使用迁移来更新它。
英文:
I have this migration where I created a foreign key the first time:
Schema::table('user_plays_quizzes', function (Blueprint $table) {
$table->bigInteger('quiz_id')->unsigned()->change();
$table->foreign('quiz_id')->references('id')->on('quizzes');
});
Now I have a new migration, where I want to update this foreign key to be nullable(true) and to be nullOnDelete().
I tried it so many times, but there are always errors, I feel like this is the closest approach:
Schema::table('user_plays_quizzes', function (Blueprint $table) {
$table->bigInteger('quiz_id')->unsigned()->nullable(true)->change();
$table->foreign('quiz_id')->references('id')->on('quizzes')->nullOnDelete();
});
Sadly it also does not work:
> SQLSTATE[HY000]: General error: 1005 Can't create table d039e62e.user_plays_quizzes (errno: 121
"Duplicate key on write or update") (SQL: alter table user_plays_quizzes add constraint user_plays_quizzes_quiz_id_foreign for
eign key (quiz_id) references quizzes (id) on delete set null)
No Idea how to solve it. I cant even drop the keys and recreate them aswell. Maybe someone has an idea how I can update it with a migration
答案1
得分: 1
为什么不能删除密钥并重新创建它？
public function up()
{
Schema::table('user_plays_quizzes', function (Blueprint $table) {
// 删除quiz_id列上的外键约束
$table->dropForeign(['quiz_id']);
// 修改quiz_id列
$table->bigInteger('quiz_id')->unsigned()->nullable(true)->change();
// 在quiz_id列上添加新的外键约束
$table->foreign('quiz_id')->references('id')->on('quizzes')->nullOnDelete();
});
}
public function down()
{
Schema::table('user_plays_quizzes', function (Blueprint $table) {
// 删除quiz_id列上的外键约束
$table->dropForeign(['quiz_id']);
// 将quiz_id列还原到之前的状态
$table->bigInteger('quiz_id')->unsigned()->nullable(false)->change();
// 还原quiz_id列上的原始外键约束
$table->foreign('quiz_id')->references('id')->on('quizzes');
});
}
英文:
Why can't you drop the key and recreate it?
public function up()
{
Schema::table('user_plays_quizzes', function (Blueprint $table) {
// Drop fk constraint on quiz_id column
$table->dropForeign(['quiz_id']);
// Alter quiz_id column
$table->bigInteger('quiz_id')->unsigned()->nullable(true)->change();
// Add new fk constraint on quiz_id column
$table->foreign('quiz_id')->references('id')->on('quizzes')->nullOnDelete();
});
}
public function down()
{
Schema::table('user_plays_quizzes', function (Blueprint $table) {
// Drop fk constraint on quiz_id column
$table->dropForeign(['quiz_id']);
// Restore quiz_id column to how it was before
$table->bigInteger('quiz_id')->unsigned()->nullable(false)->change();
// Restore original fk constraint on quiz_id column
$table->foreign('quiz_id')->references('id')->on('quizzes');
});
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论