Referential integrity

[xso]

Hello there,

I have a problem with referential integrity in InnoDB. Let's say I have three tables: Author, Book and Author_Book which joins Author to Book (a book can have multiple authors and an author can have written multiple books). I have defined foreign keys from Author_Book to Book and from Author_Book to Author. If I delete an author, the relevant record is deleted from Author_Book. If I delete a book, the relevant record is also deleted from Author_Book. However, I would also like that, if I delete an author, all books written by this author (and only this author) are also removed from the table book (and not only from Author_Book). This behaviour should of course not happen if a book has multiple authors. Is there any way I can achieve this?

Thank you in advance.

xso

 

[r937]

not with RI alone

what you're saying is to delete a parent row after the last of its children is deleted

only a trigger can do that

the other problem in a many-to-many is best illulstrated with your example -- "If I delete an author, the relevant record is deleted from Author_Book."

well, what if you delete an author who has co-authored a book? it will then appear that that book has only one author, when in fact it had two

so to delete an author properly, you have to also ensure that none of her books were co-authored

RI is great, and i always suggest that people use it wherever possible, but RI cannot do everything that needs to be done

rudy

http://r937.com/

 

[xso]

Thanks for your help. Fortunately I also have the choice to use Postgres which means that I will be able to use triggers ;-)