Laravel Migrations onDelete Restrict not working

Question

Hello I'm trying to define relationships in my migrations I'm using on delete restrict to prevent deletion of parent record when child is present. but its not working. For example I have this event table (parent) that has editions (child). I'm using event_id in editions table with onDelete('restrict') and have event_id in my edition table.. It should restrict me on deleting from events table as long record has child record in edition tables right? but its not..

Here are the migrations of both tables

Events (parent)

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateEventsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('events', function (Blueprint $table) {
            //master table
            $table->increments('event_id');
            $table->string('name');
            $table->text('full_name');
            $table->text('description');
            $table->tinyInteger('status');
            $table->integer('created_by');
            $table->integer('updated_by');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('events');
    }
}

Editions (Child)

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateEditionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('editions', function (Blueprint $table) {
            $table->increments('edition_id');
            $table->integer('event_id')->unsigned();
            $table->string('name');
            $table->dateTime('start')->nullable();
            $table->dateTime('end')->nullable();
            $table->enum('stage', ['Archived', 'Cancelled', 'Closed', 'Live', 'On-site', 'Pre-event', 'Sold out'])->nullable()->default('Pre-event');
            $table->tinyInteger('status');
            $table->integer('created_by');
            $table->integer('updated_by');
            $table->timestamps();

        });

        Schema::table('editions', function($table) {

            $table->foreign('event_id')
                ->references('event_id')->on('events')
                ->onDelete('restrict')->onUpdate('restrict');




        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('editions');
    }
}

May be this accepted answer useful for you https://stackoverflow.com/questions/13105019/how-to-add-on-delete-cascade-and-on-update-restrict-using-phpmyadmin — Ayaz Ali Shah, Mar 20 '18 at 08:56
The default constraint is a restrict so by all means use defaults https://stackoverflow.com/a/8073114/5189811 — Oluwafemi Sule, Mar 20 '18 at 09:04

score 4 · Answer 1 · answered Mar 20 '18 at 09:13

4

According to this thread :

If you are using the SoftDeletes trait, then calling the delete() method on your model will only update the deleted_at field in your database, and the onDelete constraint will not be triggered, given that it is triggered at the database level i.e. when a DELETE query is executed.

So make sure that you use DELETE not SoftDeletes otherwise you can add the constraint manually.

answered Mar 20 '18 at 09:13

Maraboc

10,550
3
37
48

I know but I have also tried deleting from Database Directly (sequel pro).. doesn't affect – AHSAN Mar 20 '18 at 09:42

score 2 · Accepted Answer · answered Mar 20 '18 at 08:54

2

One thing I see missing from your foreign key definition is adding an index on it, that is a requirement for foreign keys and it might be what's caussing issues for you.

Try changing

$table->integer('event_id')->unsigned();

to

$table->integer('event_id')->unsigned()->index();

Also, you can just add the foreign key definition immediately after the column definition, no need to put it in a different Schema::table() block.

answered Mar 20 '18 at 08:54

Tudor

1,798
2
12
21

it didn't work. My code is adding constraint. Its even showing in the database. But its not restricting on delete.. (I have tried deleting from phpmyadmin, sequel pro, application) its allowing everywhere. Even when the phpmyadmin says ON DELETE RESTRICT – AHSAN Mar 20 '18 at 10:24
Have you by any chance disabled the foreign key checks when doing said delete? Try running this SQL command on the server `SET FOREIGN_KEY_CHECKS=1;` and try again – Tudor Mar 20 '18 at 10:26
I didn't. But I restarted MySQL server and it fixed the problem.. All constraints are working now. Thanks – AHSAN Mar 20 '18 at 10:43

Laravel Migrations onDelete Restrict not working

2 Answers2