Preventing Orphan Records

This tip is a short tip about Referential Integrity and Orphan Records or, more precisely, how to prevent them.

When you establish a relationship between two tables, you have the option of setting referential integrity.

Referential integrity is set between a table that contains a primary key (or indexed field) and another table that has a related foreign key; this usually happens in a one to many relationship between the two tables.

An example of this is that a client (in one table) may have many appointments (stored in the second table); by enforcing referential integrity you cannot add an appointment for a client that does not exist in the client table. Because of this it is often assumed that you cannot add a record with no client assigned (i.e. the Foreign key content is Null), setting referential integrity does not enforce this. You can still add an orphan record in the appointment table that is not linked at all to the client table.

To prevent Null values occurring in the Foreign Key field of the second table, you need to set the property ‘Required’ to ‘Yes’ for this field.

To do this:

Open the second table in Design view, select the foreign key field and then at the bottom of the screen set the ‘Required’ property to ‘Yes’.

You will now be forced to enter a value in this field, thus preventing records in the appointment tables that do not have a null value in the foreign field.

I hope this tip, is of some benefit to you.

If you would like to learn more about referential integrity or how to set up tables correctly, then visit my Tutorial on Microsoft Access.