CODEWITHSUNDEEP
phpmysqllaravellaravel-5
zacurry
zacurry

Reputation: 906

How to drop a composite key which includes a foreign key in laravel 5.2 migration

I have a table (named j_stones) which consists of 5 fields : 

id(primary key)  
j_stones_type(foreign-key)  
shape  
size  
carat

I want to make each row unique, so I created a migration to do this :

public function up()
    {
        Schema::table('j_stones', function (Blueprint $table) {
            $table->unique(['j_stone_types_id','shape','size','carat']);
        });
    }

This works perfectly but when I try to rollback I get the following error:

1[Illuminate\Database\QueryException]

SQLSTATE[HY000]: General error: 1553 Cannot drop index

'j_stones_j_stone_types_id_shape_size_carat_unique': needed in a foreign key constraint (SQL: alter table j_stones drop index j_stones_j_stone_types_id_shape_size_carat_unique)

[PDOException] SQLSTATE[HY000]: General error: 1553 Cannot drop index 'j_stones_j_stone_types_id_shape_size_carat_unique': needed in a foreign key constraint'

This is my rollback code:

    public function down()
{
    Schema::table('j_stones', function (Blueprint $table) {
        $table->dropUnique(['j_stone_types_id','shape','size','carat']);
    });
}

I tried disabling foreign key contraint like this: 

    public function down()
{
    DB::statement('SET FOREIGN_KEY_CHECKS = 0'); 
    Schema::table('j_stones', function (Blueprint $table) {

        $table->dropUnique(['j_stone_types_id','shape','size','carat']);
    });
    DB::statement('SET FOREIGN_KEY_CHECKS = 1'); 
}

And also like this :

    public function down()
{
    Schema::disableForeignKeyConstraints();
    Schema::table('j_stones', function (Blueprint $table) {

        $table->dropUnique(['j_stone_types_id','shape','size','carat']);
    });
    Schema::enableForeignKeyConstraints();
}

But still error keeps happening on rollback.
Am using MySql with InnoDb.

Please advice.

Edit:
I got the rollback to work using the below hack , but am still looking for the proper solution : 

    public function down()
{

    Schema::table('j_stones', function (Blueprint $table) {
        $table->dropForeign(['j_stone_types_id']);
        $table->dropUnique(['j_stone_types_id','shape','size','carat']);
    });

}

Upvotes: 5

Views: 6616

Answers (3)

Ryan
Ryan

Reputation: 24035

Here is the workaround for this reported Laravel bug

public function up() {
    Schema::table('contact_tags', function (Blueprint $table) {            
        $table->unique(['tag_id', 'contact_id'], 'tag_contact_unique'); //so that contact cannot have the same tag multiple times
    });
}

public function down() {
    Schema::table('contact_tags', function (Blueprint $table) {
        //THE FOLLOWING 2 COMMANDS ARE THE WORKAROUND
        //Although this feels weird, we first need to add the missing indexes:
        $table->index('tag_id', 'tag_id_foreign');
        $table->index('contact_id', 'contact_id_foreign');

        //Now proceed with the main part of this "down" command to drop the unique index:
        $table->dropUnique('tag_contact_unique');
    });
}

Upvotes: 13

AnhellO
AnhellO

Reputation: 1059

I was facing the same issue. The way I solved it was by doing the following inside the down() function of the migration:

  1. Drop the foreign key
  2. Drop the unique composite index
  3. Create the foreign key again

E.g.

public function down()
{
    Schema::table('j_stones', function (Blueprint $table) {
        $table->dropForeign(/* Foreign key name */);
    });

    Schema::table('j_stones', function (Blueprint $table) {
        $table->dropUnique(/* Array of fields for the composite index */);
    });

    Schema::table('j_stones', function (Blueprint $table) {
        /* Creating back the foreign key here, just like the way it was before running the migration */
    });
}

This way, every time I rolled back the migration, my DB and the foreign key returned to their previous state.

Upvotes: 2

rdcl
rdcl

Reputation: 21

I just encountered the same problem and this worked for me:

    public function down()
{
    Schema::table('j_stones', function (Blueprint $table) {
        $table->index('j_stone_types_id']);
        $table->dropUnique(['j_stone_types_id','shape','size','carat']);
    });
}

So first add a new index on the column that is needed for the foreign key constraint, and then you can drop the compound key.

Upvotes: 2

Related Questions