DDC-369: Bulk-Delete on Self-Referencing CTI fails with FK Error #4522

Closed
doctrinebot opened this Issue Feb 24, 2010 · 4 comments

2 participants

@doctrinebot

Jira issue originally created by user @beberlei:

    public function testDeleteAs()
    {
        $dql = 'DELETE Doctrine\Tests\Models\Company\CompanyEmployee AS p';
        $this->_em->createQuery($dql)->getResult();

        $this->assertEquals(0, count($this->_em->createQuery(
            'SELECT count(p) FROM Doctrine\Tests\Models\Company\CompanyEmployee p')->getResult()));
    }

fails with:

Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testDeleteAs()
Exception: [PDOException] SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`doctrine*tests`.`company_persons`, CONSTRAINT `company_persons_ibfk_1` FOREIGN KEY (`spouse_id`) REFERENCES `company*persons` (`id`))
With queries:
25. SQL: 'DELETE FROM company*persons WHERE (id) IN (SELECT id FROM company_persons_id*tmp)' Params:
24. SQL: 'DELETE FROM company*employees WHERE (id) IN (SELECT id FROM company_persons_id*tmp)' Params:
23. SQL: 'DELETE FROM company*managers WHERE (id) IN (SELECT id FROM company_persons_id*tmp)' Params:
22. SQL: 'INSERT INTO company*persons_id_tmp (id) SELECT t0.id FROM company_employees t0 INNER JOIN company_persons c0_ ON t0.id = c0_.id LEFT JOIN company_managers c1_ ON t0.id = c1*.id' Params:
21. SQL: 'CREATE TEMPORARY TABLE company*persons_id*tmp (id INT NOT NULL, PRIMARY KEY(id))' Params:
20. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '15', '14'
19. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '15', '13'
18. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '15', '16'
17. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '14', '15'
16. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '14', '13'
15. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '13', '15'
14. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '13', '14'
13. SQL: 'INSERT INTO company*persons_friends (person_id, friend*id) VALUES (?, ?)' Params: '16', '15'
12. SQL: 'UPDATE company*persons SET spouse*id = ? WHERE id = ?' Params: '13', '14'
11. SQL: 'UPDATE company*persons SET spouse*id = ? WHERE id = ?' Params: '14', '13'
10. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '16', '100000', 'IT'
9. SQL: 'INSERT INTO company*managers (id, title, car*id) VALUES (?, ?, ?)' Params: '16', 'Foo', '4'
8. SQL: 'INSERT INTO company*persons (name, spouse*id, discr) VALUES (?, ?, ?)' Params: 'Roman B.', '', 'manager'
7. SQL: 'INSERT INTO company_cars (brand) VALUES (?)' Params: 'BMW'
6. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '15', '800000', 'IT2'
5. SQL: 'INSERT INTO company*persons (name, spouse*id, discr) VALUES (?, ?, ?)' Params: 'Jonathan W.', '', 'employee'
4. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '14', '400000', 'IT2'
3. SQL: 'INSERT INTO company*persons (name, spouse*id, discr) VALUES (?, ?, ?)' Params: 'Guilherme B.', '', 'employee'
2. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '13', '200000', 'IT'
Trace:
/home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/DBAL/Connection.php:630
/home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/Query/Exec/MultiTableDeleteExecutor.php:123
/home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/Query.php:198
/home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/AbstractQuery.php:511
/home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/AbstractQuery.php:349
/home/benny/code/php/wsnetbeans/Doctrine/trunk/tests/Doctrine/Tests/ORM/Functional/AdvancedDqlQueryTest.php:93
/home/benny/code/php/wsnetbeans/Doctrine/trunk/tests/Doctrine/Tests/OrmFunctionalTestCase.php:253

fixture is:

    public function generateFixture()
    {
        $car = new CompanyCar('BMW');

        $manager1 = new CompanyManager();
        $manager1->setName('Roman B.');
        $manager1->setTitle('Foo');
        $manager1->setDepartment('IT');
        $manager1->setSalary(100000);
        $manager1->setCar($car);

        $person2 = new CompanyEmployee();
        $person2->setName('Benjamin E.');
        $person2->setDepartment('IT');
        $person2->setSalary(200000);

        $person3 = new CompanyEmployee();
        $person3->setName('Guilherme B.');
        $person3->setDepartment('IT2');
        $person3->setSalary(400000);

        $person4 = new CompanyEmployee();
        $person4->setName('Jonathan W.');
        $person4->setDepartment('IT2');
        $person4->setSalary(800000);

        $person2->setSpouse($person3);

        $manager1->addFriend($person4);
        $person2->addFriend($person3);
        $person2->addFriend($person4);
        $person3->addFriend($person4);

        $this->_em->persist($car);
        $this->_em->persist($manager1);
        $this->_em->persist($person2);
        $this->_em->persist($person3);
        $this->_em->persist($person4);
        $this->_em->flush();
        $this->_em->clear();
    }
@doctrinebot

Comment created by romanb:

That should rather be ->executeUpdate() instead of ->getResult() but thats surely not the problem here.

@doctrinebot

Comment created by romanb:

An option is to check all associations and if there is a self-referential one execute UPDATE statements to set the FKs to null.

However, this is a bit of extra work and I dont consider this scenario extremely common together with the combination of bulk deletes.

Pushing priority down for now.

@doctrinebot

Comment created by @guilhermeblanco:

Added coverage to this specific situation. All passing.
Closing the ticket as fixed for 2.3.

3e601c3

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.3 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment