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.

score 23 · Answer 1 · answered Jul 05 '15 at 09:31

23

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.

answered Jul 05 '15 at 09:31

TachyonVortex

8,242
3
48
63

1

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. – jhoff Jun 02 '17 at 21:27
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? – Jairo Nov 14 '17 at 00:50
Works for Laravel 5.6 – bambamboole Feb 22 '18 at 20:09
1

@bambamboole would you please explain how did you do it? Did you added it to AppServiceProvider or to TestCase? Thanks – MrCujo Mar 02 '18 at 04:17

score 6 · Answer 2 · answered Jan 20 '16 at 14:16

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

score 6 · Answer 3 · answered Mar 02 '18 at 15:29

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 :-)

score 3 · Answer 4 · answered Sep 13 '17 at 10:15

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;

    ...

score 0 · Answer 5 · answered Mar 31 '22 at 11:17

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.

Laravel 5.1: Enable SQLite foreign key constraints

5 Answers5

Linked