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

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.

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?

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