Orphaned Rows in databases

Question

I was trying to understand the concept of orphaned rows in a database and how they can be found and removed.

Say for example you had 2 tables (person,dog) and there was a 1-to-1 relationship between them so every person must have a dog and every dog must have an owner.

Person table:

 ID      Name   
 1       James
 2       Matt
 3       Mike

Dog table:

 ID Name Owner_id
 1   Sam    1
 2   Rex    2
 3   Max    3
 4   Fred   4

Now if i joined those tables with:

 SELECT *
 FROM Person P
 FULL OUTER JOIN Dog D ON P.id = D.Owner_id

This would produce a result where dog (fred) doesnt have an owner and just appears as NULL.

Is this therefore an orphaned row as it should have a matching owner?

Is this only caused due to no primary/foreign keys?

How can orphaned rows been found in a database?

score 9 · Accepted Answer · edited Oct 09 '20 at 08:58

9

You sort of answered your own question. But I would use the following left join to find the orphaned rows:

SELECT d.*
FROM Dog d
LEFT JOIN Person p
    ON d.Owner_id = p.ID
WHERE
    p.ID IS NULL;

This would return every dog record which does not have a matching owner.

One way to avoid situations like this would be to employ foreign key constraints(like, Cascade Delete - link). With such constraints it would not be possible to delete an owner record if certain dog records were still referencing that owner.

I hope that this answer helps you to avoid barking up the wrong tree (woof).

edited Oct 09 '20 at 08:58

Ritveak

2,930
2
13
28

answered Oct 04 '17 at 10:46

Tim Biegeleisen

502,043
27
286
360

Yes, i understand thanks. What is the easiest method to find orphaned rows? – Ryan Gadsdon Oct 04 '17 at 10:58
Well I might prefer the above answer because it is conceptually clean. You could also try using `EXISTS`, which might perform a bit faster. – Tim Biegeleisen Oct 04 '17 at 11:04

Orphaned Rows in databases

1 Answers1

Linked