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 row in a related table references a non-existent row in the primary table, it is said to be an orphaned row. This is because it has no “parent” with which its data is associated with. The terms orphaned row and orphaned record tend to be used interchangeably, even though there’s a subtle difference between a row and a record.

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 row.

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.