Dropping foreign keys in Laravel migration

Question

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

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_user drop 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

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');
    }
}

Answer 1

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.

In your update migration, try this:

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

Answer 2

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 down function 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.

So the down function should look like this:

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 migrate to run the up function and php artisan migrate:rollback to run the down command and the error no longer shows.

Answer 3

I have modified your code below.

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

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']);
    });
}