Disable Doctrine Foreign Key Constraint

Question

I have a relation to 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 

Mostly my models are the page and page version. When I delete a page , I do not want to delete revisions. I also want to save page_id in page versions (i.e. Don't set it to null).

How can I do this with Doctrine?

Answer 1

In the definition, you cannot delete the record pointed to by the foreign key without setting the key to null ( onDelete="SET NULL" ) or cascading the delete operation ( there are two options - ORM level: cascade={"remove"} | base level data: onDelete="CASCADE" ).
There is an alternative to setting a default value for an existing record , but you have to do it manually, I don’t think that Doctrine supports this out-of-the-box (please correct me if I am wrong, but in this case setting a default not required).

This rigor reflects the concept of foreign key constraints; as @ Théo said:

a FK - ensure data consistency.

Soft deletion (already mentioned) is one solution, but you can also add an extra removed_page_id column that you synchronize with page_id just before you delete it in the preRemove event preRemove (Call back life cycle). Whether such information is of any value, but I believe that you have some significance for this, otherwise you would not ask this question.

I definitely do not claim that this is good practice , but it is at least what you can use for your case with the edge. So something on the line:

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, set the correct value to $removedPageId during the build of your Revision , then you don’t even have to perform a lifecycle callback on deletion.

Answer 2

You explicitly request data inconsistency, but I'm sure you really don't want this. I can’t think of a situation where this would be justified. This is bad practice and will definitely 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 of your entity (in other words: adds a column to your table) with the name deletedAt to store if (or better: when) this object is deleted. Therefore, if this attribute is null , the object is not deleted.

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

Alternatively, you can add an "activated" boolean attribute or status field (for example, "published", "draft", "deleted").

Answer 3

You can disable the export of foreign keys for certain models:

 User: attributes: export: tables columns: 

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

Answer 4

When I delete a page, I do not want to delete the changes. I also want to keep page_id on revision pages (i.e. Don't set its value to null).

I think you already received your answer: “The doctrine will not do this, simply because it is foreign to the concept of“ Foreign Keys. ”The principle of FK is to ensure data consistency, so if you have FK, it must refer to an existing one identifier: When deleting, some DB engine, such as InnoDB for MySQL, allows you to set FK to null (if you made the FK column null), but referring to a nonexistent ID is not feasible, or it is not FK.

If you really want to do this, do not use Doctrine for this particular case; this does not prevent you from using Doctrine elsewhere in your code base. Another solution is to simply discard the FK constraint manually, or use the database operator before the query to skip the FK checks.