Doctrine how to setup Many to Many to Many relationship

Question

Summary: Using the following YML I get a reference to a FOREIGN KEY (role_id) that is not in my YML.

Problem: I'm trying to create a system where there is a user (CreditName) who can belong to many role's (all of which can have many users). There are also many projects, which can contain many User+Roles, so I'd like the Project to have a Many-to-Many relationship with the entry in the refclass for the Many-to-Many relationship between Users and Roles.

Here's the relevant YML:

Project:
  columns:
    title:              { type: string(255), notnull: true, unique: true }
  relations:
    CreditNameRoles:
      class: CreditNameRole
      refClass: ProjectCreditNameRole
      foreignAlias: Projects
      onDelete: CASCADE

ProjectCreditNameRole:
  columns:
    project_id:          { type: integer, primary: true }
    credit_name_role_id: { type: integer, primary: true }
  relations:
    Project:             { foreignAlias: ProjectGenres, onDelete: CASCADE }
    CreditNameRole:      { foreignAlias: CreditNameRole, onDelete: CASCADE }

CreditName:
  columns:
    name:        { type: string(255) }
  relations:
    Roles:
      class: Role
      refClass: CreditNameRole
      foreignAlias: CreditNames
      onDelete: CASCADE

CreditNameRole:
  columns:
    id:              { type: integer}
    credit_name_id:  { type: integer, primary: true }
    role_id:         { type: integer, primary: true }
  relations:
    CreditName:      { foreignAlias: CreditNameRoles, onDelete: CASCADE }
    Role:            { foreignAlias: CreditNameRoles, onDelete: CASCADE }

Role:
  columns:
    name:            { type: string(255) }

Using doctrine/symfony to try and use this YML to generate the database I get the following:

bash-3.2$ ./symfony doctrine:build --all --no-confirmation

  SQLSTATE[HY000]: General error: 1005 Can't create table 'database_name.#sql-68_148' (errno: 150). Failing Query: "ALTER TABLE credit_name_role ADD CONSTRAINT crpc FOREIGN KEY (role_id) REFERENCES project_credit_name_role(credit_name_role_id)". Failing Query: ALTER TABLE credit_name_role ADD CONSTRAINT crpc FOREIGN KEY (role_id) REFERENCES project_credit_name_role(credit_name_role_id)  

Where is this 'FOREIGN KEY (role_id)' coming from and how do I correct it? Thanks!

Answer 1

In mysql you can't have two primary keys, though you can have multiple indexes. Maybe having two id columns with primary:true is causing the problem?

Answer 2

Maybe in ProjectCreditNameRole your foreignAliases should both be ProjectCreditNameRoles