Laravel migrations nice way of disabling foreign key checks

Question

When running laravel migrations, I am facing a small inconvenience. I use Laravel 5.1.

Since there are a lot of tables with many relationships, it is probably impossible that I rename the migration files so they run in the correct order, so no foreign key constraint is violated. This was what I did once in the past, and it was very inpractical.

What I'm doing now is defining each migration like this:

class CreateSomeTable extends Migration
{
    public function up()
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        // my table definitions go here
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }

    public function down()
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        // drop table
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }
}

The problem with this is that it's tedious to write and it clutters up the code.

I've also thought about creating two dummy migration files, whose only purpose would be to enable and disable the foreign key checks, and I would name them in such a way that they would run at the beginning and the end of each migration.

If there is an elegant solution, would it be possible to apply it to the seeding process as well, since this tends to be a problem there as well.

This is obviously a very improvised solution, and I am asking if there is a better way of doing it. Is there some beforeMigrate and afterMigrate methods that I can override or something along those lines?

And if not, how do you go about doing it?

Any insights would be appreciated, I dislike all the options I have stated.

They should always run in the correct order if you used artisan to create the migrations and they have the correct timestamps. They run in order of the timestamps.. — Karl, Dec 15 '15 at 23:36
Yes, that's what I meant when referred to the file order. The trouble is, sometimes you don't generate them in the correct order, or sometimes you could have a cyclic dependency i.e. both tables have foreign keys referring to each other. No matter in what order you run that example, you'll get a foreign key constraint violation unless you disable the checks. — Pavlin, Dec 16 '15 at 07:19
The best way to avoid this problems is to creates a migration with table creation first and then another one that creates/removes keys. — Maxim Lanin, Dec 16 '15 at 09:45
Not particularly related, but you can disable foreign key checks this way as well: `\DB::getSchemaBuilder()->disableForeignKeyConstraints()`. Not sure if there's a better way to call this method. — x-yuri, Nov 28 '16 at 18:15
I have exactly the same problem again, with yet another project. As a temporary hack I'm doing exactly as suggested in the OP; 2 dummy migration files timestamped to run before and after everything else, turning foreign key checks off and on respectively. It is my belief that Laravel's migration system is fundamentally flawed. Reviewing table creation code split between all these files is ugly and inconvienient. From now on I'm going to stick everything in a single migration file, with foreign key checks disabled and enabled at the start and end, and all schema logic laid out clearly between. — Inigo, Dec 08 '16 at 13:25

4levels · Accepted Answer · 2017-01-30T14:02:30.420

I had a similar task at hand when Lumen / Laravel started using Passport and I had to ditch the previous oauth server implementation from lucadegasperi/oauth2-server-laravel.

I finally managed to get things going by creating 2 migrations, where the first one clears foreign keys and the second one actually deletes the tables.

I had to use dates before the migrations of Laravel's Passport (2016-06-01) so they will be executed before those.

2016_05_31_000000_clear_old_oauth_relations.php

//...
class ClearOldOauthRelations extends Migration
{
    public function up()
    {
        Schema::disableForeignKeyConstraints();
        // drop foreign keys
        Schema::table('oauth_access_tokens', function (BluePrint $table) {
            $table->dropForeign('oauth_access_tokens_session_id_foreign');
        });
        //...
        Schema::enableForeignKeyConstraints();
    }
    //...
}

And in the second file 2016_05_31_000001_clear_old_oauth.php

//...
public function up()
{
    Schema::disableForeignKeyConstraints();
    Schema::drop('oauth_access_tokens');
    //...
    Schema::enableForeignKeyConstraints();
}
//...

This is really nice! Has this only been added recently, or has it been in laravel for a while and I just didn't notice it when I had this problem? — Pavlin, Jan 30 '17 at 13:54
Hi @Pavlin, I don't know when this was added in Laravel / Lumen. Note that you'll need to look up the names of the foreign key constraints in eg. PhpMyAdmin (Structure -> Relations) — 4levels, Jan 30 '17 at 14:00

score 6 · Answer 2 · edited Oct 27 '17 at 09:28

I got this done by extracting the foreign key logic into a separate migration file. This helped me to:

Disable the foreign key constraints.
Securely drop the database, if it exists.

In code:

//file: 2017_06_19_230601_fk_postuser_table.php

public function down()
{
        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('post_user');
}

score 3 · Answer 3 · answered Feb 19 '18 at 19:12

Another important aspect to remember is to drop the foreignKey FIRST, then the column. Dropping the column first throws the error:

Cannot drop index 'tableName_columnName_foreign': needed in a foreign key constraint

The proper order matters:

    public function down()
    {
        Schema::table('tableName', function (Blueprint $table) {
            $table->dropForeign(['columnName']); // fk first

            $table->dropColumn('columnName'); // then column
        });
    }

Laravel migrations nice way of disabling foreign key checks

3 Answers3

Linked