Disable Doctrine foreign key constraint

Question

I have a relationship on one of my models:

/**
* @ORM\ManyToOne(targetEntity="Page", cascade="persist")
* @ORM\JoinColumn(name="page_id", referencedColumnName="id")
*/
private $parentPage;

And when I delete the parent page, I get this error:

Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

Basically my models are a page, and page revision. When I delete the page I don't want to delete the revisions. I also want to keep the page_id on the page revisions (i.e. not set it to null).

How can I do this with Doctrine?

Solution 1

By definition you cannot delete the record that the foreign key is pointing at without setting the key to null (onDelete="SET NULL") or cascading the delete operation (There are two options - ORM Level: cascade={"remove"} | database level: onDelete="CASCADE").
There is the alternative of setting a default value of a still existing record, but you have to do that manually, I don't think Doctrine supports this "out-of-the-box" (please correct me if I am wrong, but in this case setting a default value is not desired anyway).

This strictness is reflecting the concept of having foreign key constraints; like @Théo said:

a FK is to ensure data consistency.

Soft delete (already mentioned) is one solution, but what you could also do is add an additional removed_page_id column that you sync with the page_id just before you delete it in a preRemove event handler (life cycle callback). Whether such information has any value I wonder but I guess you have some use for it, otherwise you wouldn't ask this question.

I am definitely not claiming this is good practice, but it is at least something that you can use for your edge case. So something in the line of:

In your Revision:

/**
 * @ORM\ManyToOne(targetEntity="Page", cascade="persist")
 * @ORM\JoinColumn(name="page_id", referencedColumnName="id", onDelete="SET NULL")
 */
private $parentPage;

/**
 * @var int
 * @ORM\Column(type="integer", name="removed_page_id", nullable=true)
 */
protected $removedPageId;

And then in your Page:

/** 
 * @ORM\PreRemove 
 */
public function preRemovePageHandler(LifecycleEventArgs $args)
{
    $entityManager = $args->getEntityManager();
    $page = $args->getEntity();
    $revisions = $page->getRevisions();
    foreach($revisions as $revision){
        $revision->setRemovedPageId($page->getId());
        $entityManager->persist($revision);
    }
    $entityManager->flush();
}

Alternatively you could of course already set the correct $removedPageId value during construction of your Revision, then you don't even need to execute a life cycle callback on remove.

Solution 2

I solved this by overriding one doctrine class in symfony 4.3, it looks like this for me:

<?php declare(strict_types=1);

namespace App\DBAL;

use Doctrine\DBAL\Platforms\MySQLPlatform;

/**
 * Class MySQLPlatformService
 * @package App\DBAL
 */
class MySQLPlatformService extends MySQLPlatform
{
    /**
     * Disabling the creation of foreign keys in the database (partitioning is used)
     * @return false
     */
    public function supportsForeignKeyConstraints(): bool
    {
        return false;
    }

    /**
     * Disabling the creation of foreign keys in the database (partitioning is used)
     * @return false
     */
    public function supportsForeignKeyOnUpdate(): bool
    {
        return false;
    }
}

Solution 3

You can disable the exporting of foreign keys for specific models:

User:
  attributes:
    export: tables
  columns:

Now it will only export the table definition and none of the foreign keys. You can use: none, tables, constraints, plugins, or all.

Solution 4

You are explicitly asking for data inconsistency, but I'm pretty sure you really don't want that. I can't think of a situation where this would be defensible. It is a bad practice and definitely will cause problems. For example: what is the expected result of $revision->getPage()?

There is a very simple and elegant solution: softdeletable. It basically adds an attribute to your entity (in other words: adds column to your table) named deletedAt to store if (or better: when) that entity is deleted. So if that attribute is null, the entity isn't deleted.

The only thing you have to do is add this bundle, add a trait to your entity (Gedmo\SoftDeleteable\Traits\SoftDeleteableEntity) and update your database. It is very simple to implement: this package will do the work for you. Read the documentation to understand this extension.

Alternatively, you can add an 'enabled' boolean attribute or a status field (for example 'published', 'draft', 'deleted').

Solution 5

When I delete the page I don't want to delete the revisions. I also want to keep the page_id on the page revisions (i.e. not set it to null).

I think you already got your answer: Doctrine won't do that, simply because it's alien to the notion of Foreign Keys. The principle of a FK is to ensure data consistency, so if you have a FK, it must refer to an existing ID. On delete, some DB engine such as InnoDB for MySQL allow you to put an FK to NULL (assuming you did made the FK column nullable). But referring to an inexistent ID is not doable, or it's not a FK.

If you really want to do it, don't use Doctrine for this specific case, it doesn't prevent you to use Doctrine elsewhere in your codebase. Another solution is to just drop the FK constraint manually behind or use a DB statement before your query to skip the FK checks.

View more solutions

Comments

[Stephan Vierkant]

MySQL is not a database engine, it is a DBMS. Popular database engines for MySQL (InnoDB and MyISAM) handle differently with foreign keys. If you want to know more about it, read this question: stackoverflow.com/questions/12614541/…

[Théo]

$objectManager->getConnection()->exec('SET FOREIGN_KEY_CHECKS = 0;');

[TangMonk]

Guaranteed constraints require additional computing resources. The application server is easier to scale horizontally than the database server, so the constraints should be placed in the business logic.