Deleting records that have relationships.

[Bomber2006]

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

[Hack]

How about simply flagging that customer as Not Active.

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

[DKenny]

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.

[GaryMazzone]

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

[Bomber2006]

Thanks Hack, DKenny and Mazz1.

Your comments are very helpful.