1

I have two different kinds of document. One is organized by folder. The other is organized by client. Here is the table structure, in mysql:

Table docs
ID
title

Table folderDocs
docID -> docs(ID)
folderID -> folders(ID) ON DELETE CASCADE

Table clientDocs
docID -> docs(ID)
clientID -> clients(ID) ON DELETE CASCADE

I am looking for an elegant way to delete documents automatically when either a folder or a client is deleted. The above cascade rules don't quite accomplish this. (Ie the rows in folderDocs will be deleted, but the corresponding rows in docs will remain.)

Is there anyway to set this up in mysql, using cascade rules or some other method, so that when a row from folderDocs or clientDocs is deleted by cascade, the corresponding row in docs is also deleted? (I am hoping to avoid having to programatically delete the documents first, then delete the folder / client.)

Thanks (in advance) for your help.

user1031947
  • 6,294
  • 16
  • 55
  • 88
  • This may be a dumb question, but do you have `docID` set as a foreign key in your tables? Because what you're wanting sounds like exactly what `ON DELETE CASCADE` does, but it will not work unless you have the foreign key set. – Keith Frey Mar 06 '12 at 01:02
  • why not use a DELETE trigger ? – Roger Mar 06 '12 at 01:32
  • @Rogier - I think DELETE triggers don't work if the delete is by cascade. Please correct me if I am mistaken about this. – user1031947 Mar 06 '12 at 01:56
  • Well I'm not sure either. But i think you could remove the CASCADE DELETE and implement the trigger on each table. Then is should be no problem and it should work fine. – Roger Mar 06 '12 at 09:30

2 Answers2

0

I do not think you can do that in straight sql. you might need to write a stored procedure to do it.

But is it a good idea. cos the way your tables are designed makes it look like a many to many relationship. can you delete a doc if there are still references to it from a another folder?

If they are suppose to be a one to many relationship, then the foreign key to the folder and the client should be in doc and not a table by itself. then you can use cascade delete.

Edit: you could try using a trigger instead. http://dev.mysql.com/doc/refman/5.0/en/triggers.html

but it still requires that some codes be written (in the form of triggers)

iWantSimpleLife
  • 1,944
  • 14
  • 22
0

Assuming that each document must belong to a folder or a client, then you may want to setup a scheduled cleanup job to delete all documents which do not have folders and clients using the query below

DELETE FROM docs USING docs
LEFT JOIN clientDocs c ON (c.docid = docs.id) 
LEFT JOIN folderDocs f ON (f.docid = docs.id) 
WHERE ISNULL(c.clientid) AND ISNULL(f.folderid)
Stephen Senkomago Musoke
  • 3,528
  • 2
  • 29
  • 27
  • This will work. but the problem now is that every time you query the doc out, you will need to also query the folder and client out to see if they are still valid. – iWantSimpleLife Mar 08 '12 at 01:10
  • You can add either this code to an afterDelete trigger on on clientDocs and folderDocs table so that its executed each time a document is deleted or run the query every 5 minutes from a cron job – Stephen Senkomago Musoke Mar 08 '12 at 06:02