This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.
Last Updated: 2024-11-23
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
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?