Orphan Records

To understand how orphan records work, lets first take a look at the definition or what an orphan is. An orphan is someone who’s parents are deceased or have permanently abandoned them.

Orphan records work in a similar way. An orphan record is when a child record with a foreign key points to a parent record of a primary key field that no longer exists.

Let me give you an example related to music. We have three tables, artists, songs, and a join table that we’ll call artist_songs.(Many to many because multiple artists can contribute to a song)

artist_songs join table

Here we can see the artist_songs join table. The blue box is highlighting a foreign key to the artist table that has the number 14. Lets see if that artist exists by looking at the artists table.

artists table

So far so good. We can see that in the artist table has a primary_key 14 with the band name ‘Bob and the Burners’. The child record in the join table has a parent record so it is not an orphan record.

‘Bob and the burners’ ended up being total frauds though(think milli vanilli) and the music industry decided to delete them from every single music database.

Updated artist table

Now we have a problem. The parent record no longer exists, so we have an orphan record left in the artist_songs join table. This is bad because first we have an extra row in our join table, second we have a record that is no longer referencing anything in that table, any could give us back false data when we query our database.

If we wanted to see how many artists contributed to the song with and id of 42, we would now get two related artists because of our orphan record in the join table.

If we have orphan records in our database, we have a lack of what is called referential integrity. Referential integrity means the data between two related tables will always be enforced. To help enforce referential integrity Rails has a method called dependent: destroy that we can add to the join table to ensure the join table records are destroyed when the parent records are.

class Artists < ActiveRecord
has_many: artist_songs, dependent: :destroy end