Sqlite: what is needed to re-introduce foreign key constraint support?

[webignition]

Feature Request

Foreign key constraint support is missing for sqlite. I would like to understand what can be done to allow SqlitePlatform::supportsForeignKeyConstraints() to return true.

Without such support, I find that when an entity has a property that is a foreign key, the creation of a new migration always creates a migration to drop and re-create that table and a fk-related index which is unnecessary.

Q	A
New Feature	yes (also no as this feature was added and was then reverted)
RFC	no
BC Break	no?

Summary

I'd like Doctrine\DBAL\Platforms\SqlitePlatform::supportsForeignKeyConstraints() to return true. I fully appreciate that merely changing the current return false to return true is by no means all that is needed.

Support was added in commit 85a983c3f8d6447c4441832477def1c28d57bee9 and that change was reverted before too long in commit baec4b8ea8aabe7b9c58b403d706465c897f4be9 due to matters presented in issue #4243.

My rough understanding of #4243 is that commit 85a983c3f8d6447c4441832477def1c28d57bee9 introduced a BC change which was undesirable for a minor release.

I can't quite tell if the same BC change would be acceptable for a major release or whether the same change can be introduced in a way that is not a breaking, or possibly some third option.

At present if using sqlite and when dealing with entities that make use of foreign keys (such as via a @ORM\ManyToOne annotation) my only options seem to be:

• use version 2.10.4 or greater and manually clean up every new migration
• use version 2.10.3 and be sensible so as not to trigger the issues described in SQLite Foreign Key Behavior BC Break  #4243
• stop using sqlite
• stop using foreign key relations between tables

For now I'll switch to postgres but would love to come back to using sqlite in the future.

[drupol]

Hello,

Have you tried with this event ?

To enable it:

services:
    my.listener:
        class: App\Doctrine\SqliteForeignKeyEnabler
        autowire: true
        tags:
            - { name: doctrine.event_listener, event: preFlush }

[webignition]

No, I wasn't aware that I could do that.

I'll find that a go when I have the time!

[drupol]

Just FYI, I tested locally with and without this and it seems that foreign key support is supported on my laptop.

I'm using SQLite 3.31.1

[webignition]

Excellent, that's really good to know.

[BigMichi1]

Hello,

Have you tried with this event ?

To enable it:

services:
    my.listener:
        class: App\Doctrine\SqliteForeignKeyEnabler
        autowire: true
        tags:
            - { name: doctrine.event_listener, event: preFlush }

looks like this does not work anymore with recent version of dbal (3.3.5), i can see that the event gets fired and the statement executed in logs but schema diff still does not correctly recognize foreign keys

[arnegroskurth]

Same for me - seems obvious that no command executed on the connection can change this behaviour as \Doctrine\DBAL\Schema\SchemaDiff only relies on \Doctrine\DBAL\Platforms\AbstractPlatform::supportsForeignKeyConstraints(): https://github.com/doctrine/dbal/blame/3.3.x/src/Schema/SchemaDiff.php#L150

Some ideas:

• Make the FK-support configurable for the user by introducing an appropriate property an accessor to \Doctrine\DBAL\Platforms\SqlitePlatform - might be abstractable into some kind of platform options
• Make \Doctrine\DBAL\Driver\PDO\SQLite\Driver non-final to allow overriding the platform
• Introduce a new sqlite driver variant + platform that supports foreign keys (could also automatically set PRAGMA foreign_keys = ON on connection establishment)

[morozov]

See #5427.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.