Never add a foreign key without considering cascades

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

I had the following pivot table:

<?php 

Schema::create('advisor_language', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('advisor_id')->unsigned();
    $table->foreign('advisor_id')->references('id')->on('advisors');

    $table->integer('language_id')->unsigned();
    $table->foreign('language_id')->references('id')->on('languages');
});

When I went to delete an advisor from the (not depicted) advisors table, it failed with:

 Cannot delete or update a parent row: a foreign key constraint fails
 (`project_s_production`.`advisor_language`, CONSTRAINT
 `advisor_language_advisor_id_foreign` FOREIGN KEY (`advisor_id`) REFERENCES
 `advisors` (`id`))

i.e. the fact that an advisor was referenced in the pivot table (in order to associate it with languages) prevented deletion.

But there is no good reason to keep this association around after an individual advisor is deleted. So the correct behavior would be to delete the rows referencing this advisor in the advisor_language table.

This is done by adding cascades to these foreign key constraints.

<?php 

$table->foreign('advisor_id')
  ->references('id')
  ->on('advisors')
  ->onDelete("cascade");

Now deleting an advisor returns no error — plus it also gets rid of the advisor_language entries

Lesson:

Never add a foreign key without considering cascades. Think about what you want to happen if the parent record is deleted — should the child be deleted too?