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?

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.