1

I'm doing some tests and I relate to the bands and albums tables with the foreign key. I tried deleting a row with a foreign key, but this returned parent row error My Code:

DELETE FROM BANDS WHERE idBand = '13 ';

DELETE FROM albums WHERE albumId = '13 ';

albumId is the idBand foreign key

So, how to delete a foreign key row with condition?

Yem
  • 33
  • 3
  • 1
    You need to see if the id is used somewhere else. – clinomaniac Mar 20 '18 at 22:44
  • If that band has more albums than that one (13), you cannot delete the band because it has still childs related. You must delete all the albums of that band, and then the band itself. But, you must always delete first the album and then the band, and not in the order that you write your query – James Mar 20 '18 at 22:45
  • 1
    Why are these IDs values with...spaces in them? – tadman Mar 20 '18 at 22:49
  • Don't have in code.. that spaces. – Yem Mar 20 '18 at 22:55
  • 1
    Your question is not clear. Please add the table definitions, including the foreign key definition. – The Impaler Mar 20 '18 at 23:29
  • Are you looking for https://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql? – Nico Haase Mar 21 '18 at 14:55

2 Answers2

2

you must delete from the albums table first. this is the parent table so all child rows must go first. just reverse the order of your delete. I am assuming your albums table is a child of the bands table

delete all albums for this band

delete from albums where bandid = 13

now delete the band

delete from bands where bandid = 13

now you have a songs table which is a child of a child so how do you delete all of those? You have to remove all songs for all albums for that band. Now this will have to be done before you can delete the album

delete from Songs where albumid in (select albumid from albums where bandid = 13)
Kevbo
  • 947
  • 9
  • 12
  • DELETE FROM albums WHERE albumId='13' Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`datamusic`.`musics`, CONSTRAINT `albumId` FOREIGN KEY (`albumId`) REFERENCES `albums` (`idAlbums`) ON DELETE NO ACTION ON UPDATE NO ACTION) – Yem Mar 20 '18 at 23:06
  • This is the correct answer. Are there any other tables in this schema? Is the foreign key relationship defined incorrectly? See [previous question](https://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails) – cacti5 Mar 20 '18 at 23:29
  • 1
    How far down the rabbit hole will these FKs go? `musics` references `albums`, `albums` references `bands`. As @Kevbo states you should delete child records before their parent. Alternatively, you could set your FK relationships as ON DELETE CASCADE, then you could delete the band record and the child records would be automagically removed for you. – Paul Campbell Mar 20 '18 at 23:54
  • Ok. you now have an additional child table you didnt mention so the music for each album must be deleted before the album can. You must start at the lowest level and delete upwards. as others have mentioned here you need to make sure all child records are removed before the parent row can be removed. This doesn't matter how deep you are in the hierarchy. – Kevbo Mar 21 '18 at 05:23
0

I think the OP typed the wrong sql based on his comment?

DELETE FROM albums WHERE albumId='13'

should have been

DELETE FROM albums WHERE bandId='13'

?

Peter M
  • 192
  • 5