Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007

    Need help with key design

    Hi guys...

    ....I have a very simple question, which unfortunately seems hard to answer for me

    I have two tables. Salesperson with its attributes 'ID' and 'Name'. And Invoice with several other attributes (Invoice# and another attribute are compound key). Each salesperson's ID is related to an invoice#.

    How do i have to design the structure, that, if an instance gets deleted it will not delete the salesperson. But also i do not want an instance to be deleted if a salesperson gets deleted (if he gets fired or whatever).

    because lets say, an instance exists in table "invoice" related to salesperson that does not exist anymore in table "salesperson" and a select query is made.
    an error would occur, right?

    hope it makes sense, english is not my mother tongue

    thank you

  2. #2
    Join Date
    Jun 2003
    Well, its a bad application design if you are deleting salesmen and leaving orphaned invoice records. So my first advice would be to not do this.
    But if you insist, then just do not enforce the foreign key relationship between the two tables.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Aug 2007
    i kind of figured it might be that way, to do some statistics far back regarding sales etc., even if the corresponding salesperson is not working at this company anymore.........

    so all i need to do is not defining a foreign key?

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    the way to do this is to leave the salesperson in the salespersons table forever, but with a status indicator of "fired" or "quit" or "died writing his monthly status report" or whatever

    that way, everything stays properly linked up (as regards foreign keys)

    if you do wish to delete a salesperson who still has invoices, one of the ON DELETE options is SET NULL

    this allows you to keep the deleted salesperson's invoices in the invoices table, but now you no longer know whose invoices they were (which makes sense since that salesperson doesn't exist anymore)

    regardless of which way you decide to go, under no circumstances will a select query get an error if something has been deleted that once was there before

    the select query may not show you a clear picture of the truth, but it won't give an error

    if you're worried about the salepersons table having inactive salespersons in it, you could simply create a view for active salespersons | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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