在 Laravel 迁移中删除外键

Dropping foreign keys in Laravel migration

提问by Krzysztof Michalski

I have a problem with dropping some foreign keys from my Laravel application. The problem is when I am trying to rollback the migration:

我在从 Laravel 应用程序中删除一些外键时遇到问题。问题是当我尝试回滚迁移时：

php artisan migrate:rollback

I don't know why I have errors in the console:

我不知道为什么我在控制台中出现错误：

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'role_user_user_id_foreign'; check that column/key exists (SQL: alter table role_userdrop foreign key role_user_user_id_foreign)

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'role_user_user_id_foreign'; check that column/key exists

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'role_user_user_id_foreign'; check that column/key exists

[Illuminate\Database\QueryException] SQLSTATE[42000]：语法错误或访问冲突：1091 Can't DROP 'role_user_user_id_foreign'; 检查列/键是否存在（SQL：alter table role_userdrop外键role_user_user_id_foreign）

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]：语法错误或访问冲突：1091 Can't DROP 'role_user_user_id_foreign'; 检查列/键是否存在

[PDOException] SQLSTATE[42000]：语法错误或访问冲突：1091 无法删除“role_user_user_id_foreign”；检查列/键是否存在

Below I'm showing my migration class:

下面我展示了我的迁移课程：

class UpdateRoleUserTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        schema::table('role_user',function(Blueprint $table){


            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('role_id')->references('id')->on('roles');

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('role_user', function (Blueprint $table) {
        $table->dropForeign('role_user_user_id_foreign');
        $table->dropForeign('role_user_role_id_foreign');

    });
    }
}

My table in the database has been created by migration class:

我在数据库中的表是由迁移类创建的：

class CreateRoleUserTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('role_user', function (Blueprint $table) {

            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->integer('role_id')->unsigned();

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('role_user');
    }
}

回答by Kinna T

In all of the >4.0 versions of Laravel, it allows placing column names into an array, which it will then resolve on its own. I tried to find accompanying docs, but they seem to have left it out.

在所有> 4.0 版本的 Laravel 中，它允许将列名放入一个数组中，然后它会自行解析。我试图找到随附的文档，但他们似乎忽略了它。

In your update migration, try this:

在您的更新迁移中，试试这个：

Schema::table('role_user', function (Blueprint $table) {
  $table->dropForeign(['user_id']);
  $table->dropForeign(['role_id']);
});

回答by Gharbad The Weak

I just had this issue and the problem was due to the fact that $table->dropForeign([column_name]);drops the index and not the column itself. The solution is in the downfunction drop the index in one block and then drop the actual column in a separate block. They have to be dropped in separate blocks because of something to do with not being able to drop the key in the same connection as where the column is being dropped.

我刚刚遇到了这个问题，问题是由于$table->dropForeign([column_name]);删除了索引而不是列本身。解决方案是在down函数中将索引放在一个块中，然后将实际列放在一个单独的块中。由于无法在与删除列的位置相同的连接中删除密钥，因此必须将它们放入单独的块中。

So the downfunction should look like this:

所以down函数应该是这样的：

public function down() {
    // drop the keys
    Schema::table('role_user', function (Blueprint $table) {
        $table->dropForeign(['user_id']);
        $table->dropForeign(['role_id']);
    });

   // drop the actual columns
   Schema::table('role_user', function (Blueprint $table) {
        $table->dropColumn('user_id');
        $table->dropColumn('role_id');

    });
}

now you can run php artisan migrateto run the upfunction and php artisan migrate:rollbackto run the downcommand and the error no longer shows.

现在您可以运行php artisan migrate以运行该up函数并php artisan migrate:rollback运行该down命令并且该错误不再显示。

回答by Ru Chern Chong

I have modified your code below.

我在下面修改了你的代码。

Add the onDelete()and onUpdate()to your code.

将onDelete()和添加onUpdate()到您的代码中。

public function up() 
{
    Schema::table('role_user',function(Blueprint $table) {
        $table->foreign('user_id')->references('id')->on('users')->onDelete('CASCADE')->onUpdate('CASCADE');
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('CASCADE')->onUpdate('CASCADE');
    });
}

---

---

public function down() {
    Schema::table('role_user', function (Blueprint $table) {
        $table->dropForeign(['user_id']);
        $table->dropForeign(['role_id']);
    });
}