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

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

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.

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();

    }

Advertisement

Answer