data-fixtures: Truncating table with foreign keys fails

When I am calling symfony CLI command:

php app/console doctrine:fixtures:load

I get

[PDOException] 

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (symfony.param_product, CONSTRAINT param_product_ibfk_1 FOREIGN KEY (param_val_id) REFERENCES symfony.param_value (id))

Table symfony.param_product is created by @ORM\JoinTable annotation (with @ORM\ManyToMany). I am using MySQL

This worked fine before this change: 91ff6ebbb781441c60782d90a4dd95482eeedf35

I thought it might be bug on my side (e.g. no onDelete options) but everything I tried failed.

As far as I learned e.g. from http://forums.asp.net/t/1283840.aspx/1?How+can+I+truncate+the+table+which+have+foreign+key+

 ON DELETE CASCADE is true only for deleting records and not for truncating tables.

 You have to drop the foreign key constraint from Child Table that references the Master Table to be truncated, then after only you are able to truncate the Master Table.

So it seems to me the only solution is to use DELETE or drop the foregin keys first.

Can we revert the 91ff6ebbb781441c60782d90a4dd95482eeedf35 change to fix this problem? @beberlei?

About this issue

Original URL
State: closed
Created 13 years ago
Comments: 25 (5 by maintainers)

Most upvoted comments

Here is a quick dirty hack for getting it to work with MySQL 5.5

diff --git a/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php b/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
index a580c1f..ff758c1 100644
--- a/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
+++ b/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
@@ -82,10 +82,12 @@ class ORMPurger implements PurgerInterface
             $orderedTables[] = $class->getTableName();
         }

+       $this->em->getConnection()->executeUpdate("SET foreign_key_checks = 0;");
         $platform = $this->em->getConnection()->getDatabasePlatform();
         foreach($orderedTables as $tbl) {
             $this->em->getConnection()->executeUpdate($platform->getTruncateTableSQL($tbl, true));
         }
+       $this->em->getConnection()->executeUpdate("SET foreign_key_checks = 1;");
     }

     private function getCommitOrder(EntityManager $em, array $classes)

mikael0hlsson on Jul 17, 2011

Do yourself a favor and create a script inside Symfony folder, like load_fixtures:

bin/console doc:sc:drop --force
bin/console doc:sc:cr
bin/console doc:fix:lo --no-interaction

Run chmod 755 load_fixtures and then ./load_fixtures.

sergiu-popa on Oct 27, 2016