Doctrine does not export relation properly

Question

Hi,

I've got a MySQL 5.1.41 database which i'm trying to fill with doctrine, but doctrine does not insert the relations correctly. My YAML is:

Locatie:
 connection: doctrine
 tableName: locatie
  columns:
   loc_id:
    type: integer(4)
    fixed: false
    unsigned: false
    primary: true
    autoincrement: true
  org_id:
    type: integer(4)
    fixed: false
    unsigned: false
    primary: false
    notnull: false
    autoincrement: false
  naam:
    type: string(30)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  straat:
    type: string(30)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  huisnummer:
    type: integer(4)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  huisnummer_achtervoegsel:
    type: string(3)
    fixed: false
    unsigned: false
    primary: false
    notnull: false
    autoincrement: false
  plaats:
    type: string(25)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  postcode:
    type: string(6)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  telefoon:
    type: string(12)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
  opmerking:
    type: string()
    fixed: false
    unsigned: false
    primary: false
    notnull: false
    autoincrement: false
  inloggegevens:
    type: string()
    fixed: false
    unsigned: false
    primary: false
    notnull: false
    autoincrement: false
relations:
  Organisatie:
    local: org_id
    foreign: org_id
    type: one
    onDelete: CASCADE
    onUpdate: CASCADE

Organisatie:
  connection: doctrine
  tableName: organisatie
  columns:
    org_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    naam:
      type: string(30)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    straat:
      type: string(30)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    huisnummer:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    huisnummer_achtervoegsel:
      type: string(3)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    plaats:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    postcode:
      type: string(6)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    telefoon:
      type: string(12)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    opmerking:
      type: string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Locatie:
      local: org_id
      foreign: org_id
      type: many

Now if a make an organisation and then create a location which has a foreignkey to organisation everything is fine. but when i try to update the org_id with phpmyadmin i get a contraint error. If i manually set the foreign key to ON_UPDATE CASCADE it does work.

Why does doctrine not set this option?

I got it to work in Propel, but i really want to use doctrine for this.

Answer 1

Well, you can't update the org_id because it's referenced by the location table. If you change it, it would mean that the organisation has a location that does not exist --- the foreign key fails. Insert your location data in first, and then all the location values become possible values for organisations, or alternatively use onCascade actions as you mention.

Also, something you can do is define the Organisatie table first, and include the relation declaration only for this table (one definition is enough for Doctrine). Now the Locatie table becomes referenced, and you can play with its keys freely. Of course, you still cannot reference something from the Organisatie table that does not exist.

I also spot that your schema currently says that "one organisation has many locations" although I'm guessing you've intended "one location has many organisations". If this is indeed the case, change the Organisatie->relations->Locatie to "type: one, foreignType: many", and remove the relationship declaration from your Locatie table.

Hope that helps.