How to add a constraint index in yaml Doctrine 2

Question

I`ve got two tables described in YAML.

For example :

Entities\User:
  type: entity
  table: users
  id:
    id:
      type: integer
      generator:
        strategy: AUTO
  fields:
    username:
      type: string
      length: 64
  oneToMany:
    children:
      targetEntity: UserToUser
      mappedBy: parent
    parents:
     targetEntity: UserToUser
     mappedBy: child

Entities\UserToUser:
  type: entity
  table: user_to_user
  id:
    id:
      type: integer
      generator:
        strategy: AUTO
  fields:
    user_id:
      type: integer
      nullable: false
    child_id:
      type: integer
      nullable: false
  manyToOne:
    parent:
      targetEntity: User
      inversedBy: children
      joinColumn:
        name: user_id
        referencedColumnName: id
    child:
      targetEntity: User
      inversedBy: parents
      joinColumn:
        name: child_id
        referencedColumnName: id

in this case everything generates well but in fact in database in table user_to_user there in no unique index for fields: user_id and child_id. So there is the possibility to add 2 entries with the same value.

I was trying to add constraints

uniqueConstraints:
    child_user_idx:
      columns: child_id,user_id

or 2 other ways:

id:
    user_id:
      type: integer
    child_id:
      type: integer

or

  id:
    parent:
      associationKey: true
    child:
      associationKey: true

Trying to combine these options but as the result using doctrine console validation, there were errors every time but generated SQL was exactly what I needed.

One of them for example:

The join columns of the association parent have to match to ALL identifier columns of the source entity Entities\UserToUser, however, child_id is missing.

The join columns of the association child have to match to ALL identifier columns of the source entity Entities\UserToUser, however, user_id is missing.

I do not really understand what I have to add so the validation pass correctly

what are you trying to achieve with that mapping? you want each user to have a collection of users? and an attribute that is the "parent" user? — jere
I want to find the solution to add unique index for 2 foreign keys using yaml — Ivan Petkevich

perrohunter perrohunter · Accepted Answer · 2012-04-24T19:49:42

While using YAML you can try to use the following notation:

Vendor\CategoryBundle\Entity\Category:
    type: entity
    table: category
    indexes:
        # the name of the index
        category_slug_idx:
            # Columns is an array, specify multiple columns for
            # a compound index
            columns: [ slug ]
    id:
        id:
            type: integer
            generator: { strategy: AUTO }

    fields:
        name:
            type: string
        slug:
            type: string

you can see that you can declare the indexes under the indexes: node

How to add a constraint index in yaml Doctrine 2

2 Answers