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?

use raw query `DB::statement('..')`. What is the name of your table? — Razor, Nov 08 '14 at 23:23

Farid Movsumov · Accepted Answer · 2019-01-06T11:01:26.277

159

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

edited Jan 06 '19 at 11:01

answered Nov 09 '14 at 09:44

Farid Movsumov

12,350
8
71
97

1

@BrentConnor If it is already migrated and in production, yes. Otherwise you can rollback the migration, edit the original file and then migrate again. – Marcel Gruber Sep 10 '15 at 22:32
1

At least in laravel 5.7 in $table->dropForeign('answers_user_id_foreign'); must be: $table->dropForeign(['template_id']); – D0rm1nd0 Dec 11 '18 at 13:38
8

shorter and you dont need to know the naming convention when dropping the foreign key: $table->dropForeign(['user_id']) – AngryUbuntuNerd Mar 11 '19 at 18:18
2

What will happen to data on those columns or the table, if we will drop the foreign key? – Prafulla Kumar Sahu Apr 17 '19 at 11:37
2

@PrafullaKumarSahu you are just dropping foreign key definition. You won't lose any data. – Farid Movsumov Apr 24 '19 at 09:04
It's for up() method but what should require in down() method for maintain rollback – Jatin Kaklotar May 11 '20 at 04:41
tried on laravel 7 doest not work. I've to change `$table->dropForeign('answers_user_id_foreign');` to `$table->dropForeign('user_id');` – Aslam H Apr 30 '21 at 16:01
@AslamH Laravel generates foreign keys like following `{table_name}_{local_column}_foreign`. Unless you override it – A. Khaled Jan 06 '22 at 11:46
1

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. – Shayne May 04 '22 at 07:32

score 14 · Answer 2 · answered Sep 28 '18 at 08:55

14

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

answered Sep 28 '18 at 08:55

shalonteoh

1,994
2
15
17

$table->dropForeign(['tank_id']); $table->foreign('tank_id')->references('id')->on('tanks')->onDelete('cascade')->change(); – Biswajit Biswas Nov 24 '21 at 10:37

score 7 · Answer 3 · answered Nov 08 '14 at 18:53

7

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

answered Nov 08 '14 at 18:53

Mark Baker

209,507
32
346
385

2

[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) – Farid Movsumov Nov 08 '14 at 23:16
1

Then perhaps you should report it as a bug, because that is the official [documented method](http://laravel.com/docs/4.2/schema#foreign-keys) – Mark Baker Nov 09 '14 at 00:09
OP was asking on how to add to an existing column, not how to create a new column with `onDelete`. – Camilo Aug 18 '23 at 14:59

Razor · Answer 4 · 2014-11-08T23:55:25.723

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

score 5 · Answer 5 · answered Oct 16 '15 at 17:24

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

score 1 · Answer 6 · edited Dec 25 '18 at 19:51

1

Use the unsigned function to user_id in the present migration:

$table->interger('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('table_name')->onDelete('cascade');

edited Dec 25 '18 at 19:51

finefoot

9,914
7
59
102

answered Dec 25 '18 at 18:06

Detroit Charan

161
1
2

score 1 · Answer 7 · answered Jul 05 '22 at 08:14

As of laravel 7.0 you can use $table->foreignId('user_id'); it is an alias of $table->unsignedBigInteger('user_id');

So our oneline solution to make the foreign key column delete cascade is as below:

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

score 0 · Answer 8 · answered Nov 09 '14 at 02:17

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

I am assuming you used Illuminate\Database\Schema\Blueprint::primary() to create users.id. If that is the case, then users.id will be unsigned. Therefore your foreign key column user_id must also be unsigned.

score 0 · Answer 9 · answered Sep 06 '21 at 10:23

0

Small code

$table->foreignId('user_id')->constrained('users');

answered Sep 06 '21 at 10:23

Jesvin

491
1
5
15

Bhargav Raviya · Answer 10 · 2023-04-21T11:36:56.207

0

This is a better way to create of foreign key relationship

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

OR

$table->unsignedBigInteger('product_id');
$table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');

edited Apr 21 '23 at 11:36

answered Apr 20 '23 at 08:25

Bhargav Raviya

84
6

Add "ON DELETE CASCADE" to existing column in Laravel

10 Answers10

Linked