Add "ON DELETE CASCADE" to existing column in Laravel

Question

I have user_id fk column in my table

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

I should add on cascade delete feature to this existing column. How can I do this?

Solution 1

Drop foreign key first. Thanks to Razor for this tip

$table->dropForeign('answers_user_id_foreign');
$table->foreign('user_id')
->references('id')->on('users')
->onDelete('cascade');

Solution 2

In my case, i'll need to put the col name in an array else that will be an error.

Schema::table('transactions', function (Blueprint $table) {
    $table->dropForeign(['transactions_order_id_foreign']);
    $table->foreign('order_id')
        ->references('id')->on('orders')
        ->onDelete('cascade')
        ->change();
});

mysql 5.7 ver

Solution 3

$table->foreign('user_id')
      ->references('id')->on('users')
      ->onDelete('cascade');

Solution 4

Laravel schema builder can't modify columns at the current state, so you will use raw queries. You will have to drop and recreate the constraint:

PostgreSQL

function up()
{
    DB::statement('alter table answers drop constraint answers_user_id_foreign,
                   add constraint answers_user_id_foreign
                   foreign key (user_id)
                   references users(id)
                   on delete cascade;'
    );
}
function down()
{
    DB::statement('alter table answers drop constraint answers_user_id_foreign,
                   add constraint answers_user_id_foreign
                   foreign key (user_id)
                   references users(id);'
    );
}

MySQL

function up()
{
    DB::statement('alter table answers drop FOREIGN KEY answers_user_id_foreign;');
    DB::statement('alter table answers add constraint answers_user_id_foreign
                   foreign key (user_id)
                   references users(id)
                   on delete cascade;'
    );
}
function down()
{
    DB::statement('alter table answers drop FOREIGN KEY answers_user_id_foreign;');
    DB::statement('alter table answers add constraint answers_user_id_foreign
                   foreign key (user_id)
                   references users(id);'
    );
}

Solution 5

Thanks for question answer. Help me get to this working code in L5.1 :

public function up()
{
    Schema::table('transactions', function (Blueprint $table) {
        $table->dropForeign('transactions_order_id_foreign');
        $table->foreign('order_id')
            ->references('id')->on('orders')
            ->onDelete('cascade')
            ->change();
    });

    Schema::table('orders', function (Blueprint $table) {
        $table->dropForeign('orders_user_id_foreign');
        $table->foreign('user_id')
            ->references('id')->on('users')
            ->onDelete('cascade')
            ->change();
    });
}

View more solutions

Comments

[Farid Movsumov]

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table 'xxx.#sql-5d7_226' (errno: 121) (SQL: alter table xxx add constraint answers_user_id_foreign foreign key (user_id ) references users (id) on delete cascade)

[Mark Baker]

Then perhaps you should report it as a bug, because that is the official documented method

[Marcel Gruber]

@BrentConnor If it is already migrated and in production, yes. Otherwise you can rollback the migration, edit the original file and then migrate again.

[D0rm1nd0]

At least in laravel 5.7 in $table->dropForeign('answers_user_id_foreign'); must be: $table->dropForeign(['template_id']);

[AngryUbuntuNerd]

shorter and you dont need to know the naming convention when dropping the foreign key: $table->dropForeign(['user_id'])

[Prafulla Kumar Sahu]

What will happen to data on those columns or the table, if we will drop the foreign key?

[Farid Movsumov]

@PrafullaKumarSahu you are just dropping foreign key definition. You won't lose any data.

[Jatin Kaklotar]

It's for up() method but what should require in down() method for maintain rollback

[Aslam H]

tried on laravel 7 doest not work. I've to change $table->dropForeign('answers_user_id_foreign'); to $table->dropForeign('user_id');

[Ahsan Khan]

Yes it worked. Thanks

[Biswajit Biswas]

$table->dropForeign(['tank_id']); $table->foreign('tank_id')->references('id')->on('tanks')->o‌​nDelete('cascade')->‌​change();

[A. Khaled]

@AslamH Laravel generates foreign keys like following {table_name}_{local_column}_foreign. Unless you override it

[Shayne]

Just a quick response to @MarcelGruber . Dont modify migrations, ever. Unless they are destroying data or doing something really harmful, migrations are meant to be a history of change to the db state, and when you change that history, it stops representing the state and things go bad. My rule of thumb is once its committed to git, its too late to edit, instead create a new migration.