....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
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.