Doctrine Migrations
  1. Doctrine Migrations
  2. DMIG-42

Invalid foreign key created for relationships using single table inheritance.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Labels:
      None
    • Environment:
      MySQL.

      Description

      This issue arises when associating an object that uses SINGLE_TABLE inheritance with another object. When the schema is updated, or migrations are created (I'm doing this via Symfony 2's doctrine:schema:update and doctrine:migrations:diff commands), a foreign key is added to the table that is valid for only one of the child classes.

      A quick example: The aim here is to have a "user" type that can be associated to many "books" and "videos" (and other types not shown here) via a single table named "user_content" that contains a discriminator column and the ID of a video/book/other item of content.

      /**
       * @ORM\Table(name="user_content")
       * @ORM\Entity
       * @ORM\InheritanceType("SINGLE_TABLE")
       * @ORM\DiscriminatorColumn("content_item_type", type="string")
       * @ORM\DiscriminatorMap({
       *     "Video" = "Video",
       *     "Book" = "Book"
       * })
       */
      abstract class UserContent
      {
      }
      
      /**
       * @ORM\Entity
       */
      class UserVideo extends UserContent
      {
          /**
           * @var Video 
           *
           * @ORM\ManyToOne(
           *     targetEntity="Video",
           *     inversedBy="userVideos"
           * )
           * @ORM\JoinColumn(name="content_item_id", referencedColumnName="id")
           */
          protected $contentItem;
      }
      
      /**
       * @ORM\Entity
       */
      class UserBook extends UserContent
      {
          /**
           * @var Book 
           *
           * @ORM\ManyToOne(
           *     targetEntity="Book",
           *     inversedBy="userBooks"
           * )
           * @ORM\JoinColumn(name="content_item_id", referencedColumnName="id")
           */
          protected $contentItem;
      }
      
      /**
       * @ORM\Entity
       * @ORM\Table(name="video")
       */
      class Video
      {
          /**
           * @var ArrayCollection
           *
           * @ORM\OneToMany(
           *     targetEntity="UserVideo",
           *     mappedBy="contentItem",
           *     cascade={"persist"}
           * )
           */
          protected $userVideos;
      }
      
      /**
       * @ORM\Entity
       * @ORM\Table(name="video")
       */
      class Book
      {
          /**
           * @var ArrayCollection
           *
           * @ORM\OneToMany(
           *     targetEntity="UserBook",
           *     mappedBy="contentItem",
           *     cascade={"persist"}
           * )
           */
          protected $userBooks;
      }
      
      /**
       * @ORM\Entity
       * @ORM\Table(name="user")
       */
      class User 
      {
          /**
           * @var ArrayCollection 
           *
           * @ORM\OneToMany(
           *     targetEntity="UserVideo",
           *     mappedBy="user",
           *     cascade={"persist"}
           * )
           */
          protected $userVideos;
      
          /**
           * @var ArrayCollection 
           *
           * @ORM\OneToMany(
           *     targetEntity="UserBooks",
           *     mappedBy="user",
           *     cascade={"persist"}
           * )
           */
          protected $userBooks;
      }
      
      

      When migrating/updating schema for this, you should see something like the following:

      $this->addSql("CREATE TABLE user_content (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, content_item_id INT DEFAULT NULL, date_created DATETIME NOT NULL, content_item_type VARCHAR(255) NOT NULL, INDEX IDX_241429ABA76ED395 (user_id), INDEX IDX_241429ABCD678BED (content_item_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB");
      $this->addSql("ALTER TABLE user_content ADD CONSTRAINT FK_241429ABA76ED395 FOREIGN KEY (user_id) REFERENCES user (id)");
      $this->addSql("ALTER TABLE user_content ADD CONSTRAINT FK_241429ABCD678BED FOREIGN KEY (content_item_id) REFERENCES video(id)");
      

      The first constraint is fine as "user_id" should always point at a row in the "user" table, but the second constraint is invalid: "content_item_id" will not always refer to an entry in the "video" table, it will sometimes refer to an entry in the "book" table, potentially throwing errors on inserts etc.

      If I've done something incorrectly, misunderstood how this this type of inheritance should work or if you need further details, please let me know.

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Chris Collins
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: