Orphan Records: The Ghosts Hiding in Your Database

How they sneak in, the trouble they cause, and the one rule that locks them out for good.

Every haunted house story starts the same way. Something doesn't add up. A door creaks open on its own. A light flickers in a room no one uses. Nobody can say exactly what's wrong, but everyone agrees something is.

Databases have their own version. A sales report doesn't match the accounting total. A customer swears they placed an order, but it's nowhere on their account. A report shows a line item for a product that seems to have vanished. Very often, the culprit is the same quiet ghost: the orphan record.

What Is an Orphan Record?

Most business databases are built from related tables. One customer can have many orders, and one order can have many line items. In each pair, one table is the parent and the other is the child. The child connects to its parent through a foreign key: a field that holds the parent's primary key.

An orphan record is a child whose parent doesn't exist. Picture an order detail that points to order 15, when there is no order 15. The line item is still there, taking up space and showing up in some reports, but it no longer belongs to anything real.

Order detail 4 points to order 15, which doesn't exist. That's an orphan.

How the Ghosts Sneak In

Orphans almost never appear on purpose. They creep in through ordinary, everyday actions:

• A parent gets deleted. Someone removes an old order or an inactive customer, and the child records are left behind with nowhere to go.
• A key gets mistyped. Someone enters order 51 instead of 15, and the line item ends up attached to the wrong order, or to no order at all.
• Data gets imported. Records copied in from a spreadsheet or another system arrive without their parents.
• A key field gets changed or cut off. If a parent's key changes, or a child's key field is too short to hold the full value, the connection quietly breaks.

Every one of these has the same root cause: nothing in the database is checking the relationship.

Why Orphans Are So Scary

• Totals stop matching. Queries that join parent and child tables silently skip orphans, while queries on the child table alone include them. Two reports that should agree don't.
• Lookups come up empty. A report that pulls the customer name for each order shows a blank, or nothing at all, for every orphan.
• Trust erodes. Once people catch the database being wrong, they start keeping their own spreadsheets "just to be sure," and now you have two versions of the truth.

1. Hunt Down the Orphans You Already Have

Before you can lock the door, you need to know who's already inside. In Access, a built-in wizard does the work for you.

1. On the Create tab, click Query Wizard.
2. Choose Find Unmatched Query Wizard and click OK.
3. Select the child table (for example, tblOrderDetail) and click Next.
4. Select the parent table (for example, tblOrder) and click Next.
5. Choose the matching field in each table (OrderID and OrderID), click the <=> button, and click Next.
6. Pick the fields you want to see, then click Finish. The results are your orphans.

In SQL Server, the same hunt is a short query. A LEFT JOIN keeps every child record, and the WHERE clause keeps only the ones with no matching parent:

SELECT d.OrderDetailID, d.OrderID
FROM core.OrderDetail AS d
LEFT JOIN core.[Order] AS o
    ON o.OrderID = d.OrderID
WHERE o.OrderID IS NULL
  AND d.OrderID IS NOT NULL;

The last line matters. A child with a blank foreign key isn't an orphan; it simply hasn't been assigned to a parent yet. Whether blanks are allowed is a separate decision you make when designing the table.

TechMentors recommends not allowing blanks in the foreign key of a child table. Every child should belong to a parent from the moment it's created. In Access, open the child table in Design View, select the foreign key field, and set Required to Yes. In SQL Server, define the foreign key column as NOT NULL.

Outcome: You know exactly how many orphans you have and where they are, instead of guessing why the numbers don't add up.

2. Decide What to Do with Each Orphan

There's no single right answer. Each orphan needs a decision, and it's worth involving the people who know the business:

• Reconnect it if you can tell which parent it belongs to, such as a mistyped order number.
• Restore the parent if it was deleted by mistake and you have a backup.
• Move it to an archive table if it holds history you need to keep, then remove it from the live table.
• Delete it only when you're sure it's meaningless.

Back up the database before you change anything. Cleanup is the one step you can't easily undo.

Outcome: Your data is clean, and nothing important was thrown away in the process.

3. Lock the Door with Referential Integrity

Here's the part that keeps the ghosts out for good. Referential integrity is a rule the database enforces on every single change: a child record must point to a parent that exists. Once it's turned on, the database itself refuses to create an orphan, no matter who's typing or what's being imported.

In Access:

1. Close any open tables, then on the Database Tools tab, click Relationships.
2. If the tables aren't showing, add them. Drag the primary key from the parent table (tblOrder.OrderID) onto the matching field in the child table (tblOrderDetail.OrderID).
3. In the Edit Relationships dialog box, check Enforce Referential Integrity. Leave the two Cascade boxes unchecked for now (tip 5 explains why).
4. Click Create (or OK if the relationship already existed). The line between the tables now shows a 1 on the parent side and an ∞ (infinity symbol) on the child side.

In SQL Server, referential integrity comes from a foreign key constraint:

ALTER TABLE core.OrderDetail WITH CHECK
    ADD CONSTRAINT fkOrderDetailOrder
    FOREIGN KEY (OrderID) REFERENCES core.[Order] (OrderID);

WITH CHECK tells SQL Server to verify the existing rows before creating the constraint. That's what makes the constraint trusted.

Outcome: New orphans become impossible. Every report you build from now on can rely on every child having a real parent.

4. Know Why the Door Won't Lock (Yet)

If you skip the cleanup in tip 2, you'll hit a wall here, and that's a good thing.

• Access refuses to enforce referential integrity while orphans exist. It displays a message saying the data in the table violates referential integrity rules. Go back to tip 1, clean up the orphans it finds, and try again.
• Access also requires the two fields to have matching data types. An AutoNumber primary key pairs with a Number (Long Integer) foreign key. A Short Text key pairs with Short Text, ideally of the same field size.
• SQL Server fails the WITH CHECK statement if orphans exist. Resist the temptation to switch to WITH NOCHECK. That creates the constraint without checking the existing rows, so the old orphans stay hidden and SQL Server marks the key as not trusted.

Outcome: You understand the error messages and fix the cause, instead of working around a lock that's trying to protect you.

5. Choose Your Cascade Options Carefully

When you enforce referential integrity in Access, two more checkboxes appear. SQL Server offers the same choices with ON UPDATE CASCADE and ON DELETE CASCADE.

• Cascade Update Related Fields automatically changes the foreign key in every child record when a parent's primary key changes. With an AutoNumber key, this rarely matters, because AutoNumber values can't be edited. With a key people can edit, such as a product code, it saves you from updating every child record by hand.
• Cascade Delete Related Records automatically deletes every child record when its parent is deleted. It's powerful, and it can be dangerous: deleting one customer could quietly wipe out years of order history.

Leaving Cascade Delete off doesn't create orphans. With referential integrity enforced, the database simply blocks the parent deletion until its children are dealt with. That pause is often exactly the safety check you want.

TechMentors recommends keeping both Cascade Update and Cascade Delete turned off day to day. When a key really does need to change, or a parent and its children really do need to be deleted, handle it as a planned maintenance task:

1. Get everyone out of the database.
2. Make a backup.
3. Temporarily turn on the cascade option you need.
4. Make the changes or deletions.
5. Turn the cascade option back off.
6. Let everyone back into the database.

In a split Access database, make these changes in the back-end file, where the relationships live. In SQL Server, changing a cascade option means altering the foreign key constraint, so the same plan applies: schedule it, back up first, and restore the original setting when you're done.

Outcome: Big changes happen only when you mean them to, with a backup in hand, and nobody can accidentally wipe out years of history with a single delete.

Don't Wait for Dawn

In the old tradition, the ghouls of All Hallows' Eve roamed free until the saints arrived at dawn to restore order. Your database doesn't have to wait that long. Find the orphans you have, clean them up thoughtfully, and turn on referential integrity so no new ones can ever sneak in.

Curious what this looks like in a real database? Read how we busted the ghosts in a client's database.

Worried your database might be haunted? Reach out to us and let us know how we can help you.