Dropping a composite primary key on a pivot table

Funny story. If you have found yourself in the situation where you are trying to drop a composite primary key and keep receiving some invalid constraint error then this is the lesson for you.



Introduction

With this lesson I hope to save you some time trying to solve this issue if you ever come by it.

The Issue

I was working on a Laravel 5.2 project and writing migrations for three new tables when I noticed that I have forgotten to create a primary key in a pivot table related to clients and locations called client_location. This is the schema for that table:

Schema::create('client_location', function(Blueprint $table) {
    $table->integer('client_id')->unsigned();
    $table->foreign('client_id')
        ->references('id')->on('clients')
        ->onDelete('cascade');

    $table->integer('location_id')->unsigned();
    $table->foreign('location_id')
        ->references('id')->on('locations')
        ->onDelete('cascade');
});

As you can see, no primary key. What I could have done is just add $table->increments('id'); primary key and be done with it, but then I would be able to have the same client on the same location many times recorded in the database and that is not good in my case. I only need to have a single record for this many to many relationship. To do that I need a composite primary key. No problem.

I wrote a new migration where in the up method I created that key:

Schema::table('client_location', function(Blueprint $table) {
    $table->primary(['client_id', 'location_id']);
});

But as many of you already know, you have to write the down method that does the opposite of what you have done in the up method so that you can easily migrate or rollback the database migrations.

My first attempt was very straight forward:

Schema::table('client_location', function(Blueprint $table) {
    $table->dropPrimary();
});

The way I tested that this works is I ran php artisan migrate and then followed by php artisan migrate:rollback. If the two commands were successful great, but they weren't. I received an error saying something about invalid constraint on some foreign or something like that. The error is very generic and after searching for the answer on the Internet I came up short.

Finding the solution

Then I have done what every other sane developer would do, throw a bunch of different code at it, trying to solve it:

Schema::table('client_location', function(Blueprint $table) {
    $table->dropPrimary(['client_id', 'location_id']);
});

// and

Schema::table('client_location', function(Blueprint $table) {
    $table->dropPrimary('client_id');
    $table->dropPrimary('location_id');
});

But every time the same error occurred. Great.

Finding the solution 2.0

At this point "shit got real". I have spent several hours trying to solve this and because of this bug I could not move on to actual coding. Luckily for me, on my PC I already had MySQL Workbench installed. I connected to the Homestead database that I was working on and carefully inspected that tables' changes during the migration. I have found that because the primary key is constructed from two columns that have foreign key constraint on them I was unable to drop the primary key for some reason. It still makes little sense to me, but OK. Take it as it is, I modified my down method a bit and came up with working code:

Schema::table('client_location', function(Blueprint $table) {
    $table->dropForeign('client_location_client_id_foreign');
    $table->dropForeign('client_location_location_id_foreign');
    $table->dropPrimary();
});

The Solution

First I drop the foreign key constraint and then I drop the primary key. Voila, it works! Because I dropped the foreign key constraints just to drop the primary key I had to add the foreign keys back after I deleted the primary key like so:

Schema::table('client_location', function(Blueprint $table) {
    $table->foreign('client_id')
        ->references('id')->on('clients')
        ->onDelete('cascade');
    $table->foreign('location_id')
        ->references('id')->on('locations')
        ->onDelete('cascade');
});

Now everything works and I can move on to actual coding the application. I really don't know why I could not just drop the primary key in this situation. This really is an edge situation in which you almost never come by, but it happened to me. Any insight from you guys would be welcome. Have a good week.

Credits

Comments