Laravel 5.1: Enable SQLite foreign key constraints

Question

In SQLite, foreign key constraints are disabled by default.

What's the best way to configure Laravel 5.1's SQLite database connection to enable foreign key constraints? I don't see a way of doing this in ['connections']['sqlite'] in /config/database.php.

Turquoise

Answer 1

Here's one solution. In the boot() method of App\Providers\AppServiceProvider, add:

if (DB::connection() instanceof \Illuminate\Database\SQLiteConnection) {
    DB::statement(DB::raw('PRAGMA foreign_keys=1'));
}

Thanks to @RobertTrzebinski for this blog post regarding Laravel 4.

Turquoise

Assuming most people are using sqlite for testing, you can actually enable foreign key constraints on a test-by-test basis. Because setting foreign_keys will take effect on statements prepared before the setting was changed, you can simply add this code to a helper method in your TestCase and call it when you need. – Monolayer

I'm in Laravel 5.5 and with this solution I have a strange behaivor. When I try to ->attach() ONE model to another (many to many relationship): PDOException: SQLSTATE[HY000]: General error: 1 no such table: main.cv... cvs is my table and I'm trying $this->cvs()->attach($cv); Someone has any similar problem? – Batish

Works for Laravel 5.6 – Branle

@Branle would you please explain how did you do it? Did you added it to AppServiceProvider or to TestCase? Thanks – Emmalynn

Answer 2

For me using a the facade DB within App\Providers\AppServiceProvider in Laravel 5.2 produced error. Here is my solution:

if(config('database.default') == 'sqlite'){
    $db = app()->make('db');
    $db->connection()->getPdo()->exec("pragma foreign_keys=1");
}

Kine

Answer 3

Since I only want to use this in my tests, but in all tests, I ended up with a simple implementation in the Tests\TestCase class like this:

 abstract class TestCase extends BaseTestCase
 {
        use CreatesApplication;

        protected function setUp()
        {
            parent::setUp();

            $this->enableForeignKeys();
        }

        /**
         * Enables foreign keys.
         *
         * @return void
         */
        public function enableForeignKeys()
        {
            $db = app()->make('db');
            $db->getSchemaBuilder()->enableForeignKeyConstraints();
        }
}

This works like a charm :-)

Branle

Answer 4

You could also activate foreign keys on a per test (file) basis, when the tests actually depend on tables with foreign keys.

Here's a trait: (e.g. tests/ForeignKeys.php)

<?php

namespace Tests;

trait ForeignKeys
{
    /**
     * Enables foreign keys.
     *
     * @return void
     */
    public function enableForeignKeys()
    {
        $db = app()->make('db');
        $db->getSchemaBuilder()->enableForeignKeyConstraints();
    }
}

don't forget to run the method somewhere in your test set-up chain. I added mine as an override to my TestCase: (tests/TestCase.php)

<?php

namespace Tests;

/**
 * Class TestCase
 * @package Tests
 * @mixin \PHPUnit\Framework\TestCase
 */
abstract class TestCase extends \Illuminate\Foundation\Testing\TestCase
{
    use CreatesApplication;

    ...

    /**
     * Boot the testing helper traits.
     *
     * @return array
     */
    protected function setUpTraits()
    {
        $uses = parent::setUpTraits();

        if (isset($uses[ForeignKeys::class])) {
            /* @var $this TestCase|ForeignKeys */
            $this->enableForeignKeys();
        }
    }

    ...

after that you can add it to your tests like so:

<?php

namespace Tests\Feature;

use Tests\ForeignKeys;
use Tests\TestCase;
use Illuminate\Foundation\Testing\DatabaseMigrations;

class ExampleFeatureTest extends TestCase
{
    use DatabaseMigrations;
    use ForeignKeys;

    ...

Latricelatricia

Answer 5

In newer versions of laravel, specifically 8.x and 9.x, there is a config option to turn on foreign key constraints for SQLite databases.

config/database.php

<?php

use Illuminate\Support\Str;

return [
    // ...
    'connections' => [
        'testing' => [
            'driver' => 'sqlite',
            'database' => ':memory:',
            // here
            'foreign_key_constraints' => true,
        ],
    ],
    // ...
];

In my case I had just not set that option for the testing database.

Idonah