Laravel Schema onDelete set default

Question

I have two tables with a one-to-one relationship, set by a foreign key constraint. I want to set the onDelete rule to 'set default', meaning that when a row on the foreign table is dropped, the reference value reverts to its default value. Here is my code:

Tours Table:

Schema::create('tours', function (Blueprint $table) {
    $table->increments('id')->unsigned()->index();
    $table->integer('grade')->unsigned()->default(1);
    $table->timestamps();
});

Grades Table:

Schema::create('grades', function(Blueprint $table){
    $table->increments('id')->unsigned()->index();
    $table->string('name');
    $table->timestamps();
});

Set Foreign Key:

Schema::table('tours', function (Blueprint $table) {
    $table->foreign('grade')->references('id')->on('grades')->onDelete('set default');
});

When I run my migrations, it works- no errors. However, looking at the table in HeidiSQL, it hasn't worked properly; the foreign key has been set, but the onDelete property just shows 'NO ACTION'.

Subsequently when I delete a row in the grades table, I get a foreign key constraint error.

What am I doing wrong?

Readytowear

I think it should be as ->onDelete('cascade'). – Roemer

No, that means that the child row will be deleted. I don't want it to be deleted- I want the value to revert to its default which I have specified in the creation schema which you can see above. This should be possible; please see: mssqltips.com/sqlservertip/2365/… – Readytowear

I have the same problem now. Have you fixed it? @Readytowear – Anisole

@Jed Sorry my friend, I never did get to the bottom of this. I think in this case I just wrote a script to manually fill in the default value when the row was deleted. Seems like maybe a bug, I'm surprised there hasn't been more talk about this. Feel free to start a bounty... ;) – Readytowear

Answer 1

It's been a while but.. it depends on the engine your database uses. If you use InnoDB or NDB, the syntax will be parsed but ignored.

Check this section of the MySQL documentation:

Referential Actions

...

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

You could use SET NULL instead in case this suit your needs. If not, then you could create an observer that'd perform this operation for you.

Greenshank

Answer 2

Here is the working solution.

First set the foreign key reference without any constraints.

Here is the code from the migration:
$table->foreignId('category_id');

Then set deleting model event on that model and update each related model:
Here I am updating category_id to 1 which is the default category and named as uncategorized and this can not be deleted.

Category::deleting(function ($category) {
$recipe_ids = $category->recipes->pluck('id');
Recipe::whereIn('id', $recipe_ids)->update(['category_id' => 1]);
});

Illuminometer

Answer 3

I just came here looking for the same answer, I checked the documentation at the same time to find any way to directly manage the deletion of the foreign key, and setting a default value. Unfortunately I couldn't find a direct method to do it, but to the best of my knowledge, there is a way to do it with the help of model observers. The way I believe it could work (though it's somewhat tedious, and does the job with 2 queries instead of 1, which is a bit inefficient), is basically where

1. You will have to make the foreign key attribute nullable

$table->unsignedBigInteger("grade")->nullable();

2. You can set the on delete function to set the value to null, instead of cascading

$table->foreign("grade")->references("id")->on("grades")->onDelete("set null");

3. On the model for the given migration you will have to set an observer

public static function boot() {
    parent::boot();
    self::updating(function(Tour $tour) {
        if($tour->isDirty('grade')) {
            $tour->grade = [DEFAULT VALUE HERE];
        }
    });
}

This should be able to set the value to a default value on the deletion of your grade.

NOTE: This isn't the most efficient way, and I can't confirm whether this is the only way to do it, this is the way I think it can work, and I have used, and it's the best way I know how to do it I still am a novice programmer, if there is a better way to do it, please feel free to mention me.

Holton