Laravel 5.1: enabling SQLite foreign key constraints

Question

In SQLite, foreign key constraints are disabled by default .

What is the best way to configure a Laravel 5.1 SQLite database connection to provide foreign key constraints? I see no way to do this in ['connections']['sqlite'] in /config/database.php .

Answer 1

Here is one solution. in boot() the 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.

Answer 2

For me, using the phase database in App \ Providers \ AppServiceProvider in Laravel 5.2 caused an error. Here is my solution:

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

Answer 3

You can also activate foreign keys based on each test (file) when the tests actually depend on tables with foreign keys.

Here's a sign: (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 installation 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 as follows:

 <?php namespace Tests\Feature; use Tests\ForeignKeys; use Tests\TestCase; use Illuminate\Foundation\Testing\DatabaseMigrations; class ExampleFeatureTest extends TestCase { use DatabaseMigrations; use ForeignKeys; ... 

Answer 4

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

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

It works like a charm :-)