Skip to content
Advertisement

Laravel 5.8 / Mysql: Cannot delete or update a parent row a foreign key constraint fails

I am trying to setup my tables in my MySQL database and and I am currently creating migration files using Laravel 5.8. When I run a fresh migration with

php artisan migrate

all the migrations run smoothly and my foreign key is correctly added to my table

https://i.imgur.com/HIlR4i9.png

However when I want to update my database with

php artisan migrate:refresh

I run into this error

IlluminateDatabaseQueryException : SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table if exists services)

What I’ve tried so far:

Splitting my foreign keys in different migration files so that they come after my tables that doesn’t have foreign keys in them

File architecture in visual studio code

File Architecture

adding ->onDelete(‘cascade’) on each foreign key in my up() functions (code below)

My Services migration file

class CreateServicesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('services', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->text('description');
            $table->integer('price');
            $table->boolean('multiple_times');
            $table->boolean('location');
            $table->integer('animal_type_id')->unsigned();
            $table->timestamps();
        });
    }

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

My AnimalType foreign key

class ForeignKeyAnimalTypeServices extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('services', function($table){
            $table->foreign('animal_type_id')->references('id')->on('animal_types')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('services', function(Blueprint $table){
            $table->dropForeign(['animal_type_id']);
        });
    }
}

My Service model

class Service extends Model
{
    protected $table = 'services';
    protected $fillable = [
        'name', 'description', 'price', 'multiple_times', 'location'
    ];

    public function animalType()
    {
        return $this->belongsTo('AppAnimalType');
    }
}

Although my migrations are created, I can’t refresh my migrations if I need to (or seed my table). I want to be able to refresh my files without getting this error. I am still pretty new to programming, my apologies if there is missing information.

Advertisement

Answer

In migration Class ForeignKeyAnimalTypeServices

Try:

public function down()
    {
        Schema::disableForeignKeyConstraints();
        Schema::table('services', function(Blueprint $table){
            $table->dropForeign(['animal_type_id']);
        });

        Schema::dropIfExists('services');
        Schema::enableForeignKeyConstraints();

    }

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement