Laravel 5.2 rollback database with foreign key

1
votes

I'm trying to rollback my database, but have 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 tb_levels)

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

this is my migration code:

public function up()
{
    Schema::disableForeignKeyConstraints();

    Schema::create('tb_users', function (Blueprint $table) {
        $table->engine = 'InnoDB';

        $table->increments('id_user');
        $table->string('name');
        $table->string('username');
        $table->string('email')->unique();
        $table->integer('id_level')->unsigned();
        $table->string('password', 60);
        $table->rememberToken();
        $table->boolean('activated')->default(false);
        $table->timestamps();

        $table->foreign('id_level')->references('id_level')->on('tb_levels');
    });

    Schema::enableForeignKeyConstraints();
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::disableForeignKeyConstraints();
    Schema::table('tb_users', function(Blueprint $table){
        $table->dropForeign('tb_users_id_level_foreign');
        $table->dropColumn('id_level');
    });
    Schema::drop('tb_users');
    Schema::enableForeignKeyConstraints();
}

I've tried several ways that I found in this forum, but still got that error, any help please?

2
laravelmigrationlaravel-5.2database-migration

2 Answers

2
votes

First Disable Foreign Key using this:

SET FOREIGN_KEY_CHECKS=1;

SET GLOBAL FOREIGN_KEY_CHECKS=1;

And then migrate your database.

Again Apply Foreign key constrain:

SET FOREIGN_KEY_CHECKS=0;

SET GLOBAL FOREIGN_KEY_CHECKS=0;

0
votes

Well, finally I found a way to solve that error,

First of all you need to make a migration to drop the foreign key and column, this is the code:

public function up()
{
    Schema::disableForeignKeyConstraints();

    Schema::table('tb_users', function(Blueprint $table){
        $table->dropForeign('tb_users_id_level_foreign');
        $table->dropColumn('id_level');
    });

    Schema::enableForeignKeyConstraints();
}

Then migrate it, after that It will drop the column and foreign key.

After that delete Schema::table code, save it, and run command:

php artisan migrate:reset

Well, It works but it's really an impractical way,

Hope that out there had more easy way rather than this.