Deleting from multiple tables

Question

HiI've 3 tables, year, gallery and photos. The year and gallery tables are linked to each other via the year tables primary id key and photos table is linked to the gallery table via the gallery tables primary  id key, as follows:-Year Tableyear_idyearGallery Tablegallery_idyear_idnamePhotos Tablephoto_idgallery_idimagecaptionWhat I want to be able to delete a year, all galleries associated to the year and delete all photos associated to those galleries all in one command. Where a gallery and or a photos id does not exist, I still want to be abl eto delete the year. I think I need to do this using a left join, but I'm struggling with the concept/structure of the command.Any pointers in the right direction would be greatly appreciated.Thanks.

bregent · Accepted Answer

You don't mention what DBMS you are using. If your DBMS supports it, you can create your foreign key constraints with cascading deletes. There are a few gotchas with this so make sure you fully understand what you are doing.

If you don't want to go that route, many DBMS do support multiple table deletes, but I wouldn't recommend it. Personally I would just build 3 delete statements; start with photos, then gallery, then year.

Anonymous · Answer

Totally agree with bregent.Deleting data from multiple tables in one SQL request is a very bad idea and prone to cause problems.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.