[L5] SQLite Foreign Key Support

I had the same issue, and created a workaround, but I'm not comfortable with the solution because it required me to change the Laravel source. I'm only a newbie so could not really see an 'app' way of doing it.

In config/database.php

        'sqlite' => [
            'driver'   => 'sqlite',
            'database' => storage_path().'/database2.sqlite',
            'prefix'   => '',
            'exec'     => 'PRAGMA foreign_keys = ON;',  //enable delete cascade
        ],

I added a new element 'exec'

Then in vendor ▸ laravel ▸ framework ▸ src ▸ Illuminate ▸ Database ▸ Connectors ▸ SQLiteConnector.php, replace;

return $this->createConnection("sqlite:{$path}", $config, $options);

with

    $pdo=$this->createConnection("sqlite:{$path}", $config, $options);

    //any exec statement?
    $exec = array_get($config, 'exec');
    if(isset($exec))
    {
        $pdo->exec($exec);
    }
    return $pdo;

This allows the foreign_keys property to be set each time the connection is opened, and also any additional exec statements that might be needed.

I stumbled across this, in case still looking for a 'cleaner' solution, as I was using sqlite for testing and encountered the same problem. See: http://trzebinski.info/allow-sqlite-to-use-foreign-keys-in-laravel-4/

Has anyone come up with a better solution without the need to hack?

@keevitaja This solution from TachyonVortex on SO worked for me on Laravel 5.1.20. Don't know about Laravel 4.

http://stackoverflow.com/questions/31228950/laravel-5-1-enable-sqlite-foreign-key-constraints

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

It seems like are trying to get a table named 'main.cv' instead of 'cvs' Someone has any similar problem?

@jago86 you should start your own question if you want an answer...

@Snapey , did you find a better way that doesn't involve changing vendor files?

@impbob

put somewhere (appServiceProvider boot)

DB::statement('PRAGMA foreign_keys=on');

Cheers ... I ended up updating my TestCase with

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

        // Enable foreign key support for SQLITE databases
        if (DB::connection() instanceof \Illuminate\Database\SQLiteConnection) {
            DB::statement(DB::raw('PRAGMA foreign_keys=on'));
        }
    }

@impbob Thanks a lot. Worked for me on Lumen 5.8 and in-memory SQLite DB.

Using Laravel 5.8 you can see that Foreign Key Constraints are enabled by default for SQLite:

'sqlite' => [
            'driver' => 'sqlite',
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

This essentially calls the following method in vendor/laravel/framework/src/Illuminate/Database/Schema/Grammars/SQLiteGrammar.php

/**
     * Compile the command to enable foreign key constraints.
     *
     * @return string
     */
    public function compileEnableForeignKeyConstraints()
    {
        return 'PRAGMA foreign_keys = ON;';
    }

So, to toggle this in tests you can either set it in the .env.testing file, or in the phpunit.xml file:

# .env.testing

DB_FOREIGN_KEYS=false;

# phpunit.xml

<env name="DB_FOREIGN_KEYS" value="false"/>