Laravel 5.1: Enable SQLite foreign key constraints

问题:

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.

回答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.

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

回答3:

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;

    ...

回答4:

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