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