Foreign key constraint error when refreshing migrations - Laravel

Question

I'm having a problem with migration on my Laravel project.

Since i'm fairly new to Laravel I can't figure it out.

I want to add a foreign key to an already existing table and this works, but when I refresh my migrations I get this error:

[Illuminate\Database\QueryException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key
constraint fails (SQL: drop table `battles`)

[PDOException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key
constraint fails

These are the migrations I currently have:

Table Projects

class CreateProjectsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('projects', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->string('body');
            $table->string('tags');
            $table->string('img');
            $table->string('img_tricolor');
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->timestamps();
        });
    }

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

Table Battles

class CreateBattlesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('battles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('battle_theme');
            $table->boolean('battle_active');
        });
    }

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

Adding foreign key for battles in projects

class AddProjectsBattleIdFk extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('projects', function (Blueprint $table) {
            $table->integer('battle_id')->unsigned();
            $table->foreign('battle_id')->references('id')->on('battles')->onDelete('set null');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('projects', function (Blueprint $table) {
            //
        });
    }
}

I suppose it has something to do with the battles table.

score 8 · Accepted Answer · answered Nov 29 '15 at 11:54

8

In down methods you need to remove foreign keys first:

In CreateProjectsTable

public function down()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->dropForeign('projects_user_id_foreign');
    });
    Schema::drop('projects');
}

In AddProjectsBattleIdFk

public function down()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->dropForeign('projects_battle_id_foreign');
        $table->dropColumn('battle_id');
    });
}

answered Nov 29 '15 at 11:54

Marcin Nabiałek

109,655
42
258
291

This was helpful. I also didn't realize you had to actually use "official" weird name of the foreign key when calling `dropForeign` until I read your answer more carefully. – tam5 Aug 17 '16 at 19:48

score 3 · Answer 2 · answered Nov 19 '17 at 23:12

3

I had the same problem,the Marcin's answer worked but I also found that another option is using fresh instead of refresh, in that case you don't need to remove foreign keys.

answered Nov 19 '17 at 23:12

Jeison Ortiz

31
2

score 0 · Answer 3 · answered Oct 16 '18 at 00:24

I don't know if this is more updated solution will help someone that is facing the same problem:

use this in your migration down function
Schema::disableForeignKeyConstraints();

I usually create as the last migration or name it to be sorted as last (cause migration comand runs the files under migration folder by order) something like 2099_12_32_AlterTablesCreateForeignKeysmigration where in the up function I specify all my keys for every table and at the end enable foreign keys constraints Schema::enableForeignKeyConstraints(); and then then in the down i just disabled them Schema::disableForeignKeyConstraints(); to allow the reset to truncate the tables.

Foreign key constraint error when refreshing migrations - Laravel

3 Answers3