Mysql error 1451 but no line with referenced id can be found

Question

I need to delete a row from A table. I get a 1451 error which means a constraint failed. But when i try to find this relation, nothing comes up.

mysql> delete from A where id=961;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
    constraint fails (`xxx`.`B`, CONSTRAINT `FK_D273C62CBE4E457A` FOREIGN KEY 
    (`agencementDimension_id`) REFERENCES `A` (`id`))
mysql> select * from B where agencementDimension_id=961;
Empty set (0.00 sec)

Constraints had been generated by Doctrine + Symfony 2. I have added "cascade delete" and updated schema but nothing changed "The database schema is in sync with the mapping files".

Next time if u make relationship and if you have this type requirement use ON DELETE CASCADE when creating constraint — Rajesh Patel, May 30 '16 at 10:26

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

To avoid this problem ADD ON DELETE CASCADE in your contraint

here a post showing how to do it in symfony doctrine

On delete cascade with doctrine2

edited May 23 '17 at 12:09

Community

1
1

answered May 30 '16 at 10:25

hacene abdessamed

559
6
22

I'm using Symfony + doctrine to generate schema and request, I d'ont want to mess with auto-generated constraints :/ – Shakealot May 30 '16 at 10:35
By adding "onDelete=CASCADE", mysql error message changed and i noticed i had a third table, a child of the child ! That was the problem, i added "onDelete=CASCADE" on the second relation too and it did the trick ! Thanks ! Do I need to create a message with the solution and mark it as resolved ? – Shakealot May 30 '16 at 11:22

score 0 · Answer 2 · answered May 30 '16 at 10:27

0

This error is saying that you have table A with field name 'id' and which is used somewhere as reference for field "agencementDimension_id" so you can't delete this record

you have to either delete all child rows in order to delete this record OR you can auto delete by setting ON DELETE CASCADE property on foreign key constrain

answered May 30 '16 at 10:27

Rupal Javiya

591
5
14

If you look at my second request, i didn't find any child. That's what makes me suspicious . – Shakealot May 30 '16 at 10:52
Check and follow accepted answer provided here http://stackoverflow.com/questions/14257004/doctrine2-symfony2-cascading-remove-integrity-constraint-violation-1451 http://stackoverflow.com/questions/8487042/symfony2-doctrinefixturesbundle-cant-load-fixtures-due-to-foreign-key-constra I think any of these should solve your problem – Rupal Javiya May 30 '16 at 10:58

score 0 · Accepted Answer · answered May 30 '16 at 12:15

Using Symfony 2 and Doctrine 2, I added ON DELETE CASCADE thanks to the JoinColumn onDelete property to the constraint :

 * @ORM\JoinColumn(name="agencementDimension_id", referencedColumnName="id", onDelete="CASCADE")

That changed the MySQL error message to :

mysql> delete from A where id=961;
ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`xxx`.`C`, CONSTRAINT `FK_BF92805FD55A3663` 
FOREIGN (`agencementVersionDimension_id`) REFERENCES `B` (`id`))

I had a second relation between B and C. I don't know why MySQL didn't tell me that from the start. Anyway, i have been able to delete the lines from C, which allowed me to delete lines from A !

Mysql error 1451 but no line with referenced id can be found

3 Answers3