Why does Doctrine create an index on only one column in the linking table?

Question

Just like stackoverflow, there is a many-to-many relationship between Question and Tag.

After running these symfony commands:

./symfony doctrine:drop-db
./symfony doctrine:build-db
./symfony doctrine:build-model
./symfony doctrine:build-sql
./symfony doctrine:insert-sql

With the following schema:

schema.yml

Tag:
 columns:
   name:
     type: string(10)
     notnull: true
 relations:
     Questions:
       class: Question
       foreignAlias: Tags
       refClass: QuestionTag

Question:
  columns:
    html:
     type: string(1000)
  relations:
     Tags:
       class: Tag
       foreignAlias: Questions
       refClass: QuestionTag

QuestionTag:
  columns:
    question_id:
     type: integer     
     primary: true
    tag_id:
      type: integer      
      primary: true
  relations:
    Question:
      class: Question
      foreignAlias: QuestionTags
      type: many
      foreignType: one
    Tag:
      class: Tag
      foreignAlias: QuestionTags
      type: many
      foreignType: one

In the QuestionTag linking table, which helps establish the many-to-many relationship between Tag and Question tables, I found that Doctrine only created a 'normal' index on the tag_id column. Why on this column but not on the question_id column? I don't know.

I think this is strange, because the index that Doctrine creates should be on both question_id and tag_id columns, and so it should become a 'unique' index, rather than a 'normal' one, because question_id and tag_id together form the composite primary key.

Is my understanding correct? If yes, why doesn't Doctrine do it in the correct way?

Answer 1

I believe Doctrine doesn't support composite primary (or foreign) keys. Remember, the Doctrine project is moving along extremely quickly but it's still fairly young. New features are being added all the time, though I don't think multi-column keys are planned for the 1.x branch.

The workaround would be to create a unique primary key column, question_tag_id, as one of the 3 fields in the QuestionTag table.

Answer 2

If I'm reading your code correctly, you shouldn't define the relationships again in your QuestionTag table.

And it's also possible to create an index over two columns manually (just define it in your schema), although I haven't seen how to apply a unique constraint over two columns in Doctrine.