CODEWITHSUNDEEP
mysqlsqllaravelforeign-keys
Streuh
Streuh

Reputation: 3

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

Illuminate\Database\QueryException : 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('App\AnimalType');
    }
}

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.

Upvotes: 0

Views: 4408

Answers (2)

benji127
benji127

Reputation: 345

You cannot drop a foreign key i was forced to drop all the database and recreate it, although this could help you:

ALTER TABLE table drop FOREIGN KEY transaction_client_id_foreign;

Upvotes: 0

Dev
Dev

Reputation: 6710

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

    }

Upvotes: 2

Related Questions