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

[amirpiri]

• Laravel Version: 5.3.30
• PHP Version: 7.1
• Database Driver & Version: sqlite

Description:

I have an old database that I did not write migrations for it. After a while I decided to write migration not only because I want to write unit tests, but also for development.
But my problems starts when I want to migrates from Laravel 5.2 to Laravel 5.3.
I want to update my jobs table according to the documentation for this purpose, but when I run tests again it produced an error :

`Testing started at 7:07 PM ...
/usr/bin/php7.0 /tmp/ide-phpunit.php --configuration /var/www/html/HomeLine/phpunit.xml
PHPUnit 4.8.36 by Sebastian Bergmann and contributors.

SQLSTATE[HY000]: General error: 1 no such index: jobs_queue_reserved_reserved_at_index (SQL: DROP INDEX jobs_queue_reserved_reserved_at_index)
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Connection.php:726
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Connection.php:481
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:83
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:229
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:130
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237
/var/www/html/HomeLine/database/migrations/2018_03_09_183625_upgrate_job_table_to_laravel_53.php:20
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:373
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:380
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:162
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:130
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:97
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:65
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Container/Container.php:508
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Console/Command.php:169
/var/www/html/HomeLine/vendor/symfony/console/Command/Command.php:261
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Console/Command.php:155
/var/www/html/HomeLine/vendor/symfony/console/Application.php:817
/var/www/html/HomeLine/vendor/symfony/console/Application.php:185
/var/www/html/HomeLine/vendor/symfony/console/Application.php:116
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Console/Application.php:107
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:218
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:25
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseMigrations.php:16
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/html/HomeLine/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:70
/var/www/html/HomeLine/tests/TestCase.php:31
`

I should mention that everything is OK when I run the migrations with MySql database.

[tomhatzer]

This is not really a Laravel problem. It's more a sqlite configuration setting. Sqlite has no foreign key constrains enabled per default, so you'd have to enable it by yourself.

You can enable those by executing following SQL after opening the db connection:

PRAGMA foreign_keys=1

or you can tell Laravel to enable the setting for you with the enableForeignKeyConstraints() method of the Schema class:

DB::connection()->getSchemaBuilder()->enableForeignKeyConstraints();

Also you can have another look at the ordering of your migrations. Check if there are any "parent tables" that are being dropped before their "child tables" in reference of their indexes. If so, reorder your table dropping or change your indexes.

I hope this helps.

Best wishes
Tom

[mohamed-aiman]

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

[troccoli]

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

[ashishkpoudel]

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

Did you find any work around, i'm updating my app from laravel 5.6 to 5.7 and on. All test are failing with an error SQLite doesn't support dropping foreign keys (you would need to re-create the table)

[troccoli]

@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');
       });
   }`

[begueradj]

Facing the same issue on Laravel 6.x

[begueradj]

@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']);
           });
       }
   }`

[troccoli]

@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. 😄

[begueradj]

You are right :) Thank you for catching my misunderstanding. @troccoli

[eko3alpha]

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']);
        });
    }

[iSWORD]

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']);

[Esirei]

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

[Mohammed-Alama]

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

[diegosepulveda]

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');
       });
   }

[franck-grenier]

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...
    }
}