|
Preventing Orphan Records 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), but setting referential integrity does not enforce this. You can 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 orphan records.
I hope this tip, is of some benefit to you.
|
