Deleting from multiple tables

[nicko2]

Hi

I'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 Table

year_id

year

Gallery Table

gallery_id

year_id

name

Photos Table

photo_id

gallery_id

image

caption

What 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 solution]

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.

[Guest]

Totally agree with bregent.

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

[nicko2]

Have resolved the problem using 3 seperate statements as advised. It was fairly easy in the end, I think I was over complicating things a little!

Thanks for pointing me in the right direction.