How to figure out the Foreign Key Constraints of a MySQL table using Doctrine 1.1?

Question

When two models are related to each other, Doctrine automatically generates foreign key constraints in the MySQL database beautifully. The default behaviour for onDelete and onUpdate RESTRICT, but can also be set to SET NULL or CASCADE.

Now, I want to create a migration diff from an existing database compared to a YAML file. To do this, I use the Doctrine::generateModelsFromDB() function. I use an overloaded versoin of Doctrine_Migration_Diff to do the diff work for me, that takes care of non-matching class names, MySQL field lengths etc. It does all stuff nicely, but there is a problem with these foreign key constraints.

When I create PHP classes from a MySQL database, it seems Doctrine doesn't set the 'onDelete' and 'onUpdate' to the appropriate values. When I then try to diff these PHP classes against classes that do have 'onDelete' and/or 'onUpdate' set, Doctrine tries to add a new foreign key that already exists, and MySQL fails.

I worked around this error temporarily by manually ignoring all changed foreign keys in my overloaded Doctrine_Migration_Diff, but this really is no solution.

What I want: if Doctrine thinks a new Foreign Key needs to be added, chance is it already exists, and what its onDelete and onUpdate behaviour are set to. How can I check that, if I have the local field name and the foreign field name?

P.S. If somebody is certain this was fixed in Doctrine 1.2 (which I doubt), please don't advise me to upgrade. I have specific reasons to work with 1.1 in this case, but that is a different story. I would rather be pointed into the direction of what code change provided this fix, that might be a step closer to a workaround.

Answer 1

Well Ive never had this issue. But i generally use Doctrine with Symfony and they may have customized the migration tools beyond hooking them in to the Symfony CLI interface.

If i were you i would generate a new schmea from the DB, then generate the migration from the updated schema (compares schema to models i think). Then regnerate my models afterwards.

However, Id reccommend making the schema authoritative. If you have to use a legacy DB then generate your schema from the DB when you start. Then all db changes after that, make to the schema and then migrate using schmea/models.