CODEWITHSUNDEEP
laravelmigration
Krzysztof Michalski
Krzysztof Michalski

Reputation: 831

Dropping foreign keys in Laravel migration

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

Upvotes: 21

Views: 49001

Answers (4)

MusheAbdulHakim
MusheAbdulHakim

Reputation: 354

You can use the contrained method on the foreign key to make things easy. For example:

In your up function

$table->foreignId('user_id')->nullable()->constrained()->onDelete('cascade');
$table->foreignId('role_id')->nullable()->constrained()->onDelete('cascade');

To drop any of the columns, use the dropConstrainedForeignId function as follows:

   $table->dropConstrainedForeignId('user_id');
   $table->dropConstrainedForeignId('role_id');

Upvotes: 0

Gharbad The Weak
Gharbad The Weak

Reputation: 1641

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.

Upvotes: 15

Ru Chern Chong
Ru Chern Chong

Reputation: 3756

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

Upvotes: 3

Kinna T
Kinna T

Reputation: 804

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

Upvotes: 41

Related Questions