foreign-key relationship with Doctrine concrete inheritance

In my schema, I have a generic table Animal and an inherited table Dog. Before using doctrine, I used to implement this pattern with an inherited id referencing the generic id as foreign key. I can't reproduce the same with Doctrine, and I feel like something is missing.

The schema I used to produce is the following :

CREATE TABLE `animal` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `color` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `dog` (
  `id` INT UNSIGNED NOT NULL,
  `breed` VARCHAR(40) NOT NULL,
  KEY `id` (`id`)
);

ALTER TABLE `dog` ADD CONSTRAINT FOREIGN KEY (`id`) REFERENCES `animal`(`id`);

I first tried to use Doctrine concrete inheritance, as it seemed the logical answer to this problem :

Here is the YAML file :

Animal:
  columns:
    id: { primary: true , type: integer ,  autoincrement: true }
    color: { type: string(20) , notnull: true }
Dog:
  columns:
    breed: { type: string(20) , notnull: true }
  inheritance:
    extends: Animal
    type: concrete

And the resulting SQL is :

CREATE TABLE `animal` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `color` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `dog` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `color` VARCHAR(20) NOT NULL,
  `breed` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
);

The duplication of the color column is OK, but where is the foreign key ? How do I ensure the integrity of my dog.id with my animal.id ? And what happens if I delete animal's rows ?

So I tried to use a simple one-to-one association :

Animal:
  columns:
    id: { primary: true , type: integer(10) , autoincrement: true }
    color: { type: string(20) , notnull: true }
Dog:
  columns:
    animal_id: { primary: true , type: integer(10) }
    breed: { type: string(20) , notnull: true }
  relations:
    AnimalRecord:
      class: Animal
      foreignAlias: DogRecord
      type: one
      foreignType: one
      local: animal_id
      foreign: id

The res开发者_C百科ult is the same as above (except the color column isn't duplicated, which is normal since the inheritance isn't explicited anymore), still no foreign key.

If I just change the animal_id from PRIMARY to UNIQUE, the foreign key is created from dog.animal_id to animal.id, but a new autoincrement'ed id appears.

It all behaves like being PRIMARY or FOREIGN KEY are exclusive for a column, and I can't understand why. Furthermore, it seems to me like a dangerous flaw.

---

you may want to re-read the documentation on concrete inheritance : the animal table will always be empty, because all the data you will give about the dog "medor" will be stored in the dog table when you use this inheritance strategy type. That's why there is no point to create a relation between animal and dog classes.

Animal is like an abstract class if you will.

To me, your schema should look like this:

Animal:
  columns:
    id: { primary: true , type: integer(10) , autoincrement: true }
    color: { type: string(20) , notnull: true }
Dog:
  columns:
    breed: { type: string(20) , notnull: true }