Framework: Standardized way to disable foreign key constraints

[RSully]

I am having an issue with disabling foreign key checks differently in my different environments. I am trying to use SQLite for testing and MySQL for development/production. Each has their own way to disable foreign key checks.

---

MySQL:

DB::statement('SET FOREIGN_KEY_CHECKS = 0');

SQLite:

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

[taylorotwell]

We would need to implement this at the Schema builder layer. Even then I'm not sure we can do it easily across all platforms.

[RSully]

Some info I have found (mostly unverified/untested) for each of the PDO drivers.

• MSSQL (http://stackoverflow.com/a/161410/208113):

-- Disable checks:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
-- Enable checks:
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";

• MySQL:

-- Disable checks:
SET FOREIGN_KEY_CHECKS = 0;
-- Enable checks:
SET FOREIGN_KEY_CHECKS = 1;

• SQLite:

-- Disable checks:
PRAGMA foreign_keys = OFF;
-- Enable checks:
PRAGMA foreign_keys = ON;

• 4D:

-- Disable checks:
ALTER DATABASE DISABLE CONSTRAINTS;
-- Enable checks:
ALTER DATABASE ENABLE CONSTRAINTS;

• CUBRID link a: Does not provide a hack to disable foreign key checks
• Firebird link a: Couldn't find an example but since 2.1 it may be possible.
• IBM: couldn't find any good info
• Informix link a, link b: Not sure, looks possible maybe
• Oracle link a: Looks possible maybe
• PostgreSQL link a: table by table

[ikari7789]

Wouldn't it be possible to implement this at the DB facade level?

 DB::disableForeignKeyCheck()
 DB::enableForeignKeyCheck()

And have it work on each separate DB engine in it's own engine-specific way, if any. If no specific way to do it, then simply do nothing?

[nicogominet]

+1
I totally agree with @ikari7789 this should be implemented on the DB class/façade.

[anik786]

When I tried DB::disableForeignKeyCheck(), it dosen't seem to work. Has this actually been added???

[darrensapalo]

@anik786 No, it isn't implemented yet, as proven by a simple "Find in all files" search within the repository.

The alternative is to use the code posted by the original poster/reporter depending on your sql server.

In my case, I use MySQL, so I use the following:
DB::statement('SET FOREIGN_KEY_CHECKS = 0');

[CLOUGH]

1+ for @ikari7789 DB::disableForeignKeyCheck() DB::enableForeignKeyCheck()

[CLOUGH]

Currently experiencing this issue in 5.1 so i have a little workaround inusing for the time being on till Taylor have time to integrate it inside the framework.

//DatabaseSeeder.php
public function run(){
   $this->disableForeignKeyCheck();
   //....
   $this->enableForeignKeyCheck();
}

private function disableForeignKeyCheck(){
        $connection = config('database.connections')[config('database.default')];
        $sql='';
        switch ($connection['driver']) {
            case 'mysql':
                $sql='SET FOREIGN_KEY_CHECKS = 0';
                break;
            case 'sqlite':
                $sql='PRAGMA foreign_keys = OFF';
                break;
        }

        DB::statement($sql);
    }
    private function enableForeignKeyCheck(){
        $connection = config('database.connections')[config('database.default')];
        $sql='';
        switch ($connection['driver']) {
            case 'mysql':
                $sql='SET FOREIGN_KEY_CHECKS = 1';
                break;
            case 'sqlite':
                $sql='PRAGMA foreign_keys = ON';
                break;
        }

        DB::statement($sql);
    }