can not purge table which has self-referencing #83

Closed
scourgen opened this Issue Nov 9, 2012 · 4 comments

Comments

Projects
None yet
5 participants

scourgen commented Nov 9, 2012

I have a table has following schema:

CREATE TABLE `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `area_no` bigint(20) DEFAULT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `full_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_hidden` tinyint(1) NOT NULL,
  `level` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D95DB16B727ACA70` (`parent_id`),
  CONSTRAINT `FK_D95DB16B727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `cities` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=393 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

when I execute "php app/console doctrine:fixtures:load" I got this error:

Careful, database will be purged. Do you want to continue Y/N ?y
  > purging database



  [Doctrine\DBAL\DBALException]                                                                                                              
  An exception occurred while executing 'DELETE FROM cities':                                                                                

  SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (``.`cities`, CONSTRAINT `FK_D95DB16B727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `cities` (`id`))                                      






  [PDOException]                                                                                                                             
  SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`new`.`cities`, CONSTRAINT `FK_D95DB16B727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `cities` (`id`))                                      



doctrine:fixtures:load [--fixtures[="..."]] [--append] [--em="..."] [--purge-with-truncate]

looks like when we purge table like this,we should set FOREIGN_KEY_CHECKS to 0 before execute the purge sql.

Contributor

robocoder commented Nov 22, 2012

See doctrine/dbal#57. I don't know what the "right" fix is, but my idea was to add new lifecycle events (pre- and post-truncate), and use a platform listener. (i.e., vipsoft/DoctrineDataFixturesExtension@f134db4)

skqr commented Apr 2, 2013

That wouldn't be bad at all.

I am having the exact same problem.
Any news on this ?

Member

lavoiesl commented Mar 22, 2015

Answered in #159

@lavoiesl lavoiesl added the Duplicate label Mar 22, 2015

@lavoiesl lavoiesl closed this Mar 22, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment