Categories
Laravel Quick Fix

Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

The full messy error message can look like this:

PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

This can happen when, let’s say, you have related columns Posts and Comments and you want to delete post, assuming that related comments will be swept away too. So, in migration for creating comments table you probably wrote something like that:

$table->foreignId('post_id')->references('id')->on('posts');

or

$table->foreignId('post_id')->constrained('posts');

The above means, that in Comments table you have column post_id, which is a foreign key to id in table Posts, and this is typical situation one-to-many (one post can have many comments, but one comment can have only one post). And that looks good, except when you try to delete post. Then, suddenly, you get the above error message, and nothing is deleted. Why did it happen? Well, you just forgot to add

->onDelete(‘cascade’)

in you original, earlier migration file. Just foreign key / constrained is not enough to get deleted comments related to post.
How to fix it? Well, there are several ways, but what worked for me, was to create a new migration, which can alter the comments table, with such raw SQL content like below:

public function up()
{
    Schema::table('wt_words', function (Blueprint $table) {
            DB::statement('alter table wt_words drop FOREIGN KEY wt_words_dict_id_foreign;');
            DB::statement('alter table wt_words add constraint wt_words_dict_id_foreign
                           foreign key (dict_id)
                           references wt_dictionaries(id)
                           on delete cascade;');
    });
}

/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
    Schema::table('wt_words', function (Blueprint $table) {
        DB::statement('alter table wt_words drop FOREIGN KEY wt_words_dict_id_foreign;');
        DB::statement('alter table wt_words add constraint wt_words_dict_id_foreign
                       foreign key (dict_id)
                       references wt_dictionaries(id);');
    });
}

After php artisan migrate of the above migration onDelete(‘cascade’) worked as it should.