Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Referential integrity

    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 by this author - no coauthor here) 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.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts