Deleting records that have relationships.

Hello

Can you tell me the best way to delete a record that is related to another table. For instance, I have a customer and invoice table (Customer ID is the Primary Key in Customer and the Foreign key in invoice).

I wish to delete the Customer in the Customer table but not all of its related invoices in the Invoice table.

I am using VB6 front end with an Access database.

Thanks

How about simply flagging that customer as Not Active.

If you delete the customer, to what are all of their invoices now related?

As a principle, I never want to delete records unless I really have to. As an alternative I always add a Deleted flag (a bit or boolean) to each table. Then when I need to 'delete' a record, I just set that flag to TRUE.
Then, all queries to the tables include a line in the WHERE clause that only returns records where Deleted=FALSE.

Hack and DKenny are aboslutely correct. If you have the tables related using a FK relationship (Parent-Child) then you can not have a child with out a parent (the Old relationial rules rear there head here). You can mark a customer as deleted using a feild as DKenny suggests, my only point to that would be not to use a Boolean (Yes/No) data type (if you are using MS Access). If you move to larger type RDBMS they don't all support True/False datatypes (Oracle as an example), I normmaly use a Text type of 1 charactor (stores a null (nothing) or a Y for deleted).

Thanks Hack, DKenny and Mazz1.

Your comments are very helpful.