framework: Problem dropping foreign keys when run tests using in memory sqlite.

@ashishkpoudel

Kind of. In my case I had the error when I was trying to drop a column during the drop() method in a migration. I ended up testing if the database driver was sqlite and skip the dropping of the foreign key.

public function down(): void
   {
       if (DB::getDriverName() !== 'sqlite') {
           Schema::table('teams', function (Blueprint $table) {
               $table->dropForeign(['venue_id']);
           });
       }
       Schema::table('teams', function (Blueprint $table) {
           $table->dropColumn('venue_id');
       });
   }`

just for reference Laravel 5.8

config/database.php

    'connections' => [
        'sqlite_testing' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => ':memory:',
            'prefix' => '',
            //set this env  variable to false like this
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', false),
        ],

or better in .env.testing

DB_FOREIGN_KEYS=false

I ended up adding this to my AppServiceProvider’s boot method:

Blueprint::macro('dropForeignSafe', function ($args) {
    if (app()->runningUnitTests()) {
        // Do nothing
        /** @see Blueprint::ensureCommandsAreValid */
    } else {
        $this->dropForeign($args);
    }
});

Then I replaced my calls to dropForeign with dropForeignSafe:

$table->dropForeignSafe(['user_id']);

Another solution is to catch the exception :

public function up() {
    try {
        Schema::table('table', function (Blueprint $table) {
            $table->dropForeign('table_id_foreign');
        });
    } catch (Throwable) {
        // some DB cannot drop foreign keys...
    }
}

I was running into the same issue with Laravel 7, I went with a similar approach to @troccoli. The main difference is I’m checking the environment value DB_CONNECTION, and the location of my IF statement.

    public function down()
    {
        Schema::table('clients', function (Blueprint $table) {
            if (env('DB_CONNECTION') !== 'sqlite') {
                $table->dropForeign('clients_timezone_id_foreign');
            }
            $table->dropColumn(['timezone_id']);
        });
    }

Just fought with this issue for the past hour, and what I’ve noticed is that you should be using the RefreshDatabase trait when working with sqlite. DatabaseMigrations trait is for DuskTestCases in which SQLite in-memory databases may not be used. Documentation

This doesn’t work for me for my Dusk tests.

@iSWORD Have you been unit testing this macro…??

@begueradj

No I shouldn’t, that’s the whole point of the down() method in the migration.

I want to remove the venue_id column, but to do that I had to remove the foreign key first. But SQLite doesn’t allow me.

So, if I’m using SQLite, i.e. during testing, I just remove the column (and SQLite is fine with that). If not, i.e. while using the app, I remove the foreign key first and then I can drop the column.

Hope this helps. 😄

If I drop the entire column, is then necessary also to remove the foreign key? Why not just dropColumn and i forget the if

public function down(): void
   {
       if (DB::getDriverName() !== 'sqlite') {
           Schema::table('teams', function (Blueprint $table) {
               $table->dropForeign(['venue_id']);
           });
       }
       Schema::table('teams', function (Blueprint $table) {
           $table->dropColumn('venue_id');
       });
   }

@troccoli I think you should remove the second drop column action, otherwise it will be execute even if we use SQLite:

public function down(): void
   {
       if (DB::getDriverName() !== 'sqlite') { // Keep just this
           Schema::table('teams', function (Blueprint $table) {
               $table->dropForeign(['venue_id']);
           });
       }
   }`