Framework: Adding foreign key constraint silently fails during alter in sqlite

[Crazycolorz5]

• Laravel Version: laravel/lumen-framework 5.6.4
• PHP Version: 7.2.4
• Database Driver & Version: doctrine/dbal 2.7.1

Description:

I already have a table, say table a. I add a new table b. I want to modify table a with a b_id that's a foreign key that refers to b.id and has onDelete('restrict').

I use artisan to create a migration, within which I place:

Schema::table('a', function(Blueprint $table) {
    $table->unsignedInteger('b_id')->nullable()->index();
    $table->foreign('b_id')->references('id')->on('b')->onDelete('restrict');
}

(I'm unable to create a migration with not null, but I may add another call to the schema builder to add the not null constraint).

However, upon testing this code by creating a b object and an a object that owns it, I find that I am able to delete the a instance before deleting the b instance.

Using .schema on the sqlite3 database shows that there is no foreign key constraint in the a table.

Steps To Reproduce:

1) Use Sqlite3 as the database and enable foreign keys.
2) Create migrations to create tables a and b both with an incrementing primary key id.
3) Create a new migration. Within the migration, add the code shown above.

The expected behavior is that a foreign key constraint should be added. However looking into the database shows that one has not been.

Speculation and Relevant Code

It may be an issue with Laravel calling dbal.
Here we can see a lack of foreign key compilation. This may be correct behavior, though, as this stack overflow answer notes that column alters are not supported in sqlite.
It appears the dbal does handle this properly as the answer suggests it should be done.
In terms of testing there is a test for adding foreign keys at create time, but there are no tests for adding a foreign key constraint to an existing table.

[staudenmeir]

SQLite doesn't support this feature natively: https://www.sqlite.org/omitted.html

We would have to drop and re-create the table – as we do when you change a column. Not sure how much work that would require.

If we don't support it, we should at least throw an exception to warn the user.

[SjorsO]

I proposed a solution to prevent situations like this last week: https://github.com/laravel/ideas/issues/1254

[staudenmeir]

@SjorsO Your proposal addresses a different problem. This issue is about foreign keys not getting created.

[Crazycolorz5]

Edited steps to reproduce to explicitly note enabling foreign keys.

[pvm]

I have the same problem here with delete on cascade. It doesn't work. I did a repository with the code so people can take a look:

https://github.com/pvm/laravel-sqlite-test

[jameswmcnab]

Also having this problem, bit of gotcha because I don't think the docs mention this not being supported on SQLite.

[driesvints]

There's now an option to enable foreign keys for sqlite. Documentation was updated here: https://github.com/laravel/docs/pull/4702

You'll have to upgrade to 5.7

[jameswmcnab]

@driesvints Sorry but could you re-read this issue, the PR you mention does not resolve this problem.

[driesvints]

@jameswmcnab my bad! Thanks for letting me know.

[midweste]

This bug still exists. Ran into it when running migrations during unit testing while using an sqlite file db that has a foreign key specified in the migration. Package Waavi/translation.

[someother1]

Same problem here.

A rename of any column in the table will 'reset' all the foreign keys.

(internal there will be a __temp__{tablename} table created - with all the data to transfer while migration - after transfer the normal index will be created, but no foreign keys will be handled anymore.)

[taylorotwell]

Adding foreign keys to existing tables in SQLite is not something we are going to support. Namely, because SQLite itself doesn't support it and I don't feel like doing weird hacks to make it feel like it supports it. If you need this feature, use a database that supports it.

If you want it supported, you are asking in the wrong repository - ask the SQLite maintainers to support the feature.

[SjorsO]

PR https://github.com/laravel/framework/pull/32583 attempted to make it throw an exception instead of silently failing, but the PR was rejected.