What is an Orphaned Record?

An orphaned record is a record whose foreign key value references a non-existent primary key value.

Orphaned records are a concept within database relationships. If a record in a related table references a non-existent record in the primary table, it is said to be an orphaned record. This is because it has no “parent” with which its data is associated with.

Example

If we delete record number 15 in a primary table, but there’s still a related table with the value of 15, we end up with an orphaned record.

Screenshot of a diagram depicting an orphaned record.
Here, the related table contains a foreign key value that doesn’t exist in the primary key field of the primary table. This has resulted in an “orphaned record”.

 

This is not good referential integrity. Referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

Fortunately, most relational database management systems provide the ability to enforce referential integrity. By enforcing referential integrity on all relationships, you eliminate the possibility orphaned records.

Note that orphaned records don’t apply to the primary table – only to related tables. Values can appear in a primary key field without appearing in a related table. In the above example, we have a value of 2 in the primary table but not in the related table. This is normal (and in fact, required), as a record must first exist in the primary key field before it can be referenced by a foreign key field.