Foreign Key Constraints does not work with Doctrine/Symfony and SQLite

Question

I am working with Doctrine 2.4.2, Symfony 2.4 and SQLite 3.8.3.

I have two entities defined:

Category:
    type: entity
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    oneToMany:
        ministries:
            targetEntity: Ministry
            cascade: [persist]
            mappedBy: category

And

Ministry:
    type: entity
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    manyToOne:
        category:
            targetEntity: Category
            inversedBy: ministries
            joinColumn:
                name: category_id
                nullable: false
                onDelete: CASCADE

But when I delete a category, the ministry entities do not get deleted, although the constraint should cascade. What am I missing?

Do I have to configure anything to get that working?

Solution found, [redirect to answer](http://stackoverflow.com/questions/43356717/symfony-doctrine2-manytomany-relationship-not-removed-specific-to-sqlite) — Alex83690, Apr 13 '17 at 20:37

score 6 · Accepted Answer · edited Apr 19 '17 at 08:40

6

You may need to make sure that PRAGMA foreign_keys = ON is set before deleting.

Note that this is a connection setting, not a database setting.

Addition:
With Symfony's event subscriber it is possible to execute this command in the preFlush event of Doctrine, before any writing queries are executed.

edited Apr 19 '17 at 08:40

stollr

6,534
4
43
59

answered Apr 07 '14 at 22:45

Mark Baker

209,507
32
346
385

Keep in mind that even if you do this, you still can not rely on Schema Tools for foreign keys constraint additions. They're turned off permanently – Reza S Dec 10 '19 at 16:19

score 1 · Answer 2 · answered Jan 28 '20 at 06:23

For others coming here via Google to find out why foreign keys are not enforced in doctrine.

Foreign key support in doctrine dbal SqlitePlatform has been reverted/removed as per this comment since version 2.4.0-BETA1.

This caused for me quite some bug search in my code base simply to find out that doctrine itself removed the feature due to instability.

score 0 · Answer 3 · answered Apr 07 '14 at 22:45

0

Change to cascade: ['persist', 'remove']

answered Apr 07 '14 at 22:45

seferov

4,111
3
37
75

Yes, that's possible, but just a workaround. It does not make sure that the database won't get into an invalid state. – stollr Apr 10 '14 at 09:54

score 0 · Answer 4 · answered Dec 07 '21 at 14:14

Foreign key checks are disabled by default on pdo_sqlite driver. As it's mentioned here:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.

You should enable it before flushing via an EeventSubscriber:

<?php declare(strict_types=1);

namespace App\EventSubscriber;

use Doctrine\Bundle\DoctrineBundle\EventSubscriber\EventSubscriberInterface;
use Doctrine\ORM\Event\PreFlushEventArgs;
use Doctrine\ORM\Events;

/**
 * Class SqlitePreFlushSubscriber.
 */
final class SqlitePreFlushSubscriber implements EventSubscriberInterface
{
    public function preFlush(PreFlushEventArgs $args): void
    {
        if ('sqlite' !== \strtolower($args->getEntityManager()->getConnection()->getDatabasePlatform()->getName())) {
            return;
        }

        $args->getEntityManager()->getConnection()->exec('PRAGMA foreign_keys = ON;');
    }

    /**
     * @return string[]
     */
    public function getSubscribedEvents(): array
    {
        return [Events::preFlush];
    }
}

Foreign Key Constraints does not work with Doctrine/Symfony and SQLite

4 Answers4