Deleting Records

Seldom is it necessary or a wise idea to delete records. However, sometimes it is necessary, such as the removal of time records incorrectly entered or duplicate clients. When you go to delete a record it is important to understand the concept of referential integrity.

Referential integrity is a method whereby records in a database are tied together by relations. Sometimes these relations prohibit the deletion of a record if another record is present. This can happen on two levels, the field and the record. For example, you cannot delete a funding code if there are client records that have that value on them. This is a field level protection. You cannot delete a client record if that case has a trust record associated with it. Otherwise the trust record would become an ‘orphan’ and there would be no way to know the name of the client associated with it. This is on a record level.

This doesn’t mean that records or field values can’t be deleted. All it means is that you first have to clean up any relations. In the above example, you would first need to determine which records had that funding code and change them to a different funding code. This can be easily done with an update query. In the case of the client record, you would first have to decide what to do with the associated records, such as trust and PBI, delete them, then delete the client record.

When you try to delete a record or field that has associated records in the database, you will normally get a message telling you what the problem with the deletion is. Once you clean up these problems, you can delete the record or field value. This is one reason the CurrentR field exists in many of the supporting tables beginning with sub. This value allows you to ‘shut off’ a value so users can’t use it on current records. However it preserves the meaning for the old records. For example, if you had a funding code 6 for United Way and lost the funding, you could mark the field CurrentR in subFunds false. This means it wouldn’t show up on the intake sheet as a choice. Yet when you ran reports, you could still get these values out.