Symfony2 Doctrine schema update fails

Question

I created database on my local machine. After moving my project to server I imported backup from local (because I had some important data there).

Now,when I'm trying to update schema on my server it gives my this output:

php app/console doctrine:schema:update --force
Updating database schema...





  [Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException]                                                                                                             
  An exception occurred while executing 'ALTER TABLE golf_course ADD CONSTRAINT FK_EC96E162F1503E2B FOREIGN KEY (golf_id) REFERENCES golf (id)':                               
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`GolfFairway`.`#sql-3fae_7ccf1`, CONSTRAINT `FK_EC9  
  6E162F1503E2B` FOREIGN KEY (`golf_id`) REFERENCES `golf` (`id`))                                                                                                             






  [Doctrine\DBAL\Driver\PDOException]                                                                                                                                          
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`GolfFairway`.`#sql-3fae_7ccf1`, CONSTRAINT `FK_EC9  
  6E162F1503E2B` FOREIGN KEY (`golf_id`) REFERENCES `golf` (`id`))                                                                                                             






  [PDOException]                                                                                                                                                               
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`GolfFairway`.`#sql-3fae_7ccf1`, CONSTRAINT `FK_EC9  
  6E162F1503E2B` FOREIGN KEY (`golf_id`) REFERENCES `golf` (`id`))                                                                                                             

Why this happens ? Is there a solution ?

Answer 1

@maxian

Michael Villeneuve answer is not totally right. In case of a production environnement or kind of , you just can t drop schema and recreate it.

The only way to perform it on your current schema is by the followings :

1. php app/console doctrine:schema:update --dump-sql . Copy the ouptut. Its the direct SQL queries to update your schema
2. connect mysql with mysql command line or through a mysql client
3. Disable foreign keys checking by call this query : "set foreign_key_checks=0;"
4. put the queries from doctrine:schema:update
5. Enable back foreign key checking with : "set foreign_key_checks=1;"

i cannot guarantee you won t lost some keys but you don t drop your datas at all .