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

    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.

    xso

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

Posting Permissions

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