How do I specify a mysql cascade rule for the following...

Question

In mysql, I've got the following:

TABLE docs (
    ID
    title
    content
)

TABLE specialDocs (
    docID -> docs(ID)
    userID -> users(ID)
)

So specialDocs is a subset of documents that can belong to users.

I am wondering: Is it possible to specify a cascade rule so that when a user is deleted, the docs that belong to that user are automatically deleted? (I realize it would be simple to do this if the pointer to users(ID) was a column within the table "docs". However I am unclear if it is possible with a join table like the above...)

Thanks in advance for your help

Answer 1

I wouldn't do that - your specialdocs table is a many-to-many table, so multiple users could be related to the same document. What you want, shouldn't be allowed without deleting the other references first - which a cascade delete won't do. And if you don't have referential integrity (IE: MyISAM table), then you'll have orphaned records in specialdocs to a document that no longer exists.

Cascade on delete is a referential integrity feature, so it won't be available for MyISAM tables - you'd have to use a trigger for that. Given the need to check for referential dependencies, I'd use a trigger for Innodb tables as well to get rid of the related specialdocs records first...