Database Reference
In-Depth Information
Deleting records by using queries
Over time, some of the information stored in a database might become obsolete. For ex-
ample, the Products table lists all the products the company currently offers for sale or has
sold in the past. You can indicate that a product is no longer available for sale by placing a
check mark in a Discontinued field. Discontinued products aren't displayed in the catalog or
offered for sale, but they are kept in the database for a while in case it becomes practical to
sell them again. A similar situation could exist with customers who haven't placed an order
in a long time or who have asked to be removed from a mailing list but might still place
orders.
To maintain an efficient database, it is a good idea to discard outdated records from time to
time. You could scroll through the tables and delete records manually, but if all the records
you want to delete match some pattern, you can use a delete query to quickly get rid of all
of them.
It is important to keep two things in mind when deleting records from a database:
▪
You can't recover deleted records.
▪
The effects of a delete query can be more far-reaching than you intend.
If the table from which you are deleting records is related to another table, and the Cascade
Delete Related Records option for that relationship is selected, records in the second table
will also be deleted. (
Cascade Delete
essentially means that the deletion is also applied to
related records.) Sometimes this is what you want, but sometimes it isn't. For example, you
probably don't want to delete records of previous sales at the same time that you delete
discontinued products.
As a precaution, before actually deleting records, you might want to display the Relationships
page by clicking the Relationships button in the Relationships group on the Database Tools
tab. If the table you are deleting data from has a relationship with any table containing in-
formation that shouldn't be deleted, right-click the relationship line, click Edit Relationship,
and make sure that if the Enforce Referential Integrity check box is selected, the Cascade
Delete Related Records check box is
not
selected.
As a further safeguard against potential problems, you will want to back up your database
before deleting the records. You might also want to create a new table (perhaps named
Deleted<ile name>
) and then move the records you want to delete to the new table, in
which you can review them before deleting them permanently.
