How to specify foreign key column for Class Table Inheritance in Doctrine2?

Question

How can I specify the columns that are used for the foreign key relation with Class Table Inheritance in Doctrine 2? For example, take the following two classes:

/**
 * @Entity
 * @InhertanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @DiscriminatorMap("person" = "Person", "employee" = "Employee")
 */
class Person
{
    /** @Id */
    public $id;

    /** @Column(type="string") */
    public $ssn;
}

/** @Entity */
class Employee
{
    /** @Column(type="decimal") */
    public $salary;
}

With this, Doctrine expects a table structure somewhat along this:

CREATE TABLE `person` (
    `id` INT(11) NOT NULL auto_increment,
    `ssn` VARCHAR(255) default NULL,
    PRIMARY_KEY(`id`)
)

CREATE TABLE `employee` (
    `person_id` INT(11) NOT NULL,
    `salary` DECIMAL(10,2) default NULL,
    PRIMARY_KEY(`person_id`)
)
ALTER TABLE `employee`
    ADD CONSTRAINT `person_fk` FOREIGN KEY (`person_id`)
        REFERENCES `person` (`id`) ON DELETE CASCADE

There is a foreign key employee.person_id that points to person.id. But how can I tell Doctrine which columns to use? I assume that the person.id reference comes from the @Id annotation on the Person class, but what if I want to create the FK to person.ssn instead (it's conceivable, since SSN are naturally unique).

And what if I have a legacy database where employee.person_id is called employee.p_id instead?

I think they have to be identical (subclass with same primary key as parent), I remember some talk about this being a limitation at the moment. Will try and find a reference to that later hopefully. — Orbling, Jul 21 '11 at 15:01
Thanks. It sounds odd though. I believe `doctrine orm:schema-tool:create` wanted to create a table with `employee.person_id` as the foreign key, not `employee.id`. I'll check tomorrow when I am back at work. — Sander Marechal, Jul 21 '11 at 18:53

score 3 · Accepted Answer · answered Oct 12 '11 at 18:29

First of all, you are missing the "extends Person" in the Employee class definition.

From what I know, when you are doing this type of inheritance, doctrine uses the primary key in the main class to join all other tables, its is expected behavior.

When you do a query, doctrine will join all the child tables to the parent and then hydrate according to the discriminator map. With that in mind, it doesn't matter if your primary key is an automatically incremental id or a unique field. If you have ssn defined in the parent class, it will be available for searching in all your subclasses.

So a couple of tips here. You can if you want, remove the automatical id and use ssn as your primary key, then doctrine expects that all of your child tables to have that same field defined in them. For performance it may be wise to have an integer instead of a 255 string to do the joins anyway.

If you want to mantain the automated id, you may want to add a unique index to the parent class, that way if you access the classes by that field, you wont get performance slowdowns.

If you want to have the class name to be something and the table name to be something else, use this

/** @Id @Column(name="p_id") */
public $id;

But then remember that all the tables that are part of the inheritance should use that name.

Moreover, I normally use doctrine for mapping an existing database and extending it (migrate to doctrine), and if I'm adding a new feature and the model requires so, I create the mapping and tables myself keeping in mind how Doctrines inheritance works. But if you have existing tables that think can be modeled with Inheritance, expect some trouble, and maybe the needs to modify the existing tables.

Hope this helps.

How to specify foreign key column for Class Table Inheritance in Doctrine2?

1 Answers1