doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 12:39:00 +01:00 by admin · 4 comments
admin commented 2026-01-22 12:39:00 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Feb 24, 2010).

Originally assigned to: @guilhermeblanco on GitHub.

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();
    }
Originally created by @doctrinebot on GitHub (Feb 24, 2010). Originally assigned to: @guilhermeblanco on GitHub. 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(); } ```
admin closed this issue 2026-01-22 12:39:01 +01:00
admin commented 2026-01-22 12:39:02 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Feb 24, 2010):

Comment created by romanb:

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

@doctrinebot commented on GitHub (Feb 24, 2010): Comment created by romanb: That should rather be ->executeUpdate() instead of ->getResult() but thats surely not the problem here.
admin commented 2026-01-22 12:39:04 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Feb 24, 2010):

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 commented on GitHub (Feb 24, 2010): 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.
admin commented 2026-01-22 12:39:04 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 23, 2012):

Comment created by @guilhermeblanco:

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

3e601c3a53

@doctrinebot commented on GitHub (May 23, 2012): Comment created by @guilhermeblanco: Added coverage to this specific situation. All passing. Closing the ticket as fixed for 2.3. https://github.com/doctrine/doctrine2/commit/3e601c3a53226ce981fe82db164f297a8d605ae7
admin commented 2026-01-22 12:39:04 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 23, 2012):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (May 23, 2012): Issue was closed with resolution "Fixed"
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#460
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?