MySQL InnoDB Cascade Rule that looks at 2 columns?

Question

I have the following MySQL InnoDB tables...

TABLE foldersA (
 ID
 title
)

TABLE foldersB (
 ID
 title
)

TABLE records (
 ID
 folderID
 folderType
 title
)

folderID in table "records" can point to ID in either "foldersA" or "foldersB" depending on the value of folderType. (0 or 1).

I am wondering:

Is there a way to create a CASCADE rule such that the appropriate rows in table records are automatically deleted when a row in either foldersA or folderB is deleted?

Or in this situation, am I forced to have to delete the rows in table "records" programatically?

Thanks for you help!

Answer 1

Typically,

If you have an A type of something and a B type of something, you should create a table for "folders" and then a "foldersA" and "foldersB" to hold data unique to each type. Then you can create a foreign key constraint from "records" like you want which points to "folders". If you set a CASCADE DELETE constraint on "foldersA" and on "foldersB" which points to "folders"(ID) then deleting the parent record will delete the additional record in the appropriate child table.

You other alternative would be to use a TRIGGER on both "foldersA" and "foldersB" which checks "records" for child rows and deletes them.