douhui3330
douhui3330
2016-09-06 22:59

禁用Doctrine外键约束

已采纳

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • drvfqr5609 drvfqr5609 5年前

    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.

    点赞 评论 复制链接分享
  • dsvq5069 dsvq5069 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.

    点赞 评论 复制链接分享
  • doubi7346 doubi7346 5年前

    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').

    点赞 评论 复制链接分享
  • doushan2224 doushan2224 5年前

    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.

    点赞 评论 复制链接分享

相关推荐