我有一个名为j_stones
的表,它由5个字段组成:
id(primary key)
j_stones_type(foreign-key)
shape
size
carat
我想让每一行都是唯一的,所以我创建了一个迁移来做到这一点:
public function up()
{
Schema::table('j_stones', function (Blueprint $table) {
$table->unique(['j_stone_types_id','shape','size','carat']);
});
}
这工作完美,但当我试图回滚时,我得到以下错误:
1(照亮数据库 QueryException)
SQLSTATE[HY000]: General error: 1553 Cannot drop index
' j_stones_j_stone_type_id_shape_size_carat_unique ':需要SQL: alter table
j_stones
drop indexj_stones_j_stone_types_id_shape_size_carat_unique
)[PDOException] SQLSTATE[HY000]: General error: 1553 Cannot drop索引' j_stone_j_stone_type_id_shape_size_carat_unique ':需要外键约束'
我的回滚代码:
public function down()
{
Schema::table('j_stones', function (Blueprint $table) {
$table->dropUnique(['j_stone_types_id','shape','size','carat']);
});
}
我尝试像这样禁用外键约束:
public function down()
{
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
Schema::table('j_stones', function (Blueprint $table) {
$table->dropUnique(['j_stone_types_id','shape','size','carat']);
});
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
还有这样的:
public function down()
{
Schema::disableForeignKeyConstraints();
Schema::table('j_stones', function (Blueprint $table) {
$table->dropUnique(['j_stone_types_id','shape','size','carat']);
});
Schema::enableForeignKeyConstraints();
}
但是回滚时仍然出现错误。
我使用MySql与InnoDb。
请建议。
编辑:
我得到了回滚工作使用下面的hack,但我仍在寻找适当的解决方案:
public function down()
{
Schema::table('j_stones', function (Blueprint $table) {
$table->dropForeign(['j_stone_types_id']);
$table->dropUnique(['j_stone_types_id','shape','size','carat']);
});
}
下面是这个报告的Laravel bug的解决方法
public function up() {
Schema::table('contact_tags', function (Blueprint $table) {
$table->unique(['tag_id', 'contact_id'], 'tag_contact_unique'); //so that contact cannot have the same tag multiple times
});
}
public function down() {
Schema::table('contact_tags', function (Blueprint $table) {
//THE FOLLOWING 2 COMMANDS ARE THE WORKAROUND
//Although this feels weird, we first need to add the missing indexes:
$table->index('tag_id', 'tag_id_foreign');
$table->index('contact_id', 'contact_id_foreign');
//Now proceed with the main part of this "down" command to drop the unique index:
$table->dropUnique('tag_contact_unique');
});
}
我刚刚遇到了同样的问题,这对我有用:
public function down()
{
Schema::table('j_stones', function (Blueprint $table) {
$table->index('j_stone_types_id']);
$table->dropUnique(['j_stone_types_id','shape','size','carat']);
});
}
因此,首先在列上添加外键约束所需的新索引,然后可以删除复合键。
我也面临着同样的问题。我解决这个问题的方法是在迁移的down()
函数中执行以下操作:
- 删除外键
- 删除唯一的综合索引
- 重新创建外键
。
public function down()
{
Schema::table('j_stones', function (Blueprint $table) {
$table->dropForeign(/* Foreign key name */);
});
Schema::table('j_stones', function (Blueprint $table) {
$table->dropUnique(/* Array of fields for the composite index */);
});
Schema::table('j_stones', function (Blueprint $table) {
/* Creating back the foreign key here, just like the way it was before running the migration */
});
}
这样,每次回滚迁移时,我的DB和外键都会返回到它们以前的状态。