Laravel foreign key onDelete('cascade') not working

Question

I have a many-to-many relationship between User & Role, with a role_user table. My migrations are setup as so (simplified):

users table:

public function up()
{
    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('email')->unique();
    });
}

roles table:

public function up()
{
    Schema::create('roles', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name');
    });
}

role_user table:

public function up()
{
    Schema::create('role_user', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->integer('role_id')->unsigned();
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
    });
}

So as per the docs, I set my foreign keys to unsigned.

Now, I add a couple of users, and attach some roles - everything works fine. However, when I delete a user (User::destroy(2)) the rows for that user in the role_user table do not get deleted, which is causing redundant rows.

What am I doing wrong?

• MySQL + InnoDB

EDIT: Grabbing the model and applying ->delete(); also has the same effect.

Answer 1

Try setting when trying to create this table. This fix has worked for me.

$table->engine = 'InnoDB';

I have filed a bug under: https://github.com/laravel/framework/issues/8730

Answer 2

It is established by Jake's answer that you have set default engine to InnoDB

$table->engine = 'InnoDB';

Instead of doing it in each migration file, You can do so in config/database.php

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => 'InnoDB',
    ],

Answer 3

Check the MySQL config. my.ini may still have default-storage-engine=MYISAM. Set to default-storage-engine=InnoDB and you should avoid the trouble of adding this fix to each migration.

Answer 4

I know this is an old issue, but recently (on Laravel 8) I had the same problem even when using InnoDB.

I solved it by using the constrained() option on the relationship. Without the "constrained" parameter, the migration won't create the actual relationship on the database.

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

Answer 5

For someone that's here and using SoftDeletes on their models; onDelete('cascade') functionality is lost when using SoftDeletes. Options you could use in such a case are:

1. Using dyrynda/laravel-cascade-soft-deletes package to handle this for you.
2. Using Eloquent Events to trigger an event that the parent has been deleted and handle the deletion of the child rows yourself.

Answer 6

even after changing the engine to InnoDB in Config->Database it does not work

and the solution for that is to change the migration

From

Schema::create('users', function (Blueprint $table) { $table->BigIncrements('id');

To

Schema::create('users', function (Blueprint $table) { $table->increments('id');