The Software Version of Bambi is Falsifying Your Data.

How to seek/destroy/avoid Orphan Records.

Amanda Klusmeyer
By Amanda Klusmeyer
February 15, 2022

What a sad situation. A lonely child record has a foreign key that points to a parent record of a primary key that no longer exists. Sob

How could we, as software developers, let this happen?

Imagine we have three tables: dogs, walkers, and a join table that is called dog_walkers. Great, everything is going well so far and no childhood trauma has been caused - yet. Yay!

The table dog_walkers includes two fields called dog_id and walker_id. The id’s you see in the table are the foreign keys.

Those foreign keys correspond to the same id, called primary keys, in the walkers and dogs table. Hypothetically speaking, the walkers record (primary key of 4) could decide to be more of a cat person and quit their dog walking job. Done. Finito. See ya later. Unfortunately, this record is deleted on the walkers table.

Big problem since the dog_walkers table is still referencing that record and it now no longer exists. Let’s say the owner of the dog with the id of 5 wants to see who has been walking her dog. Either no data is returned or false data is given. False data can be given in a situation where a new dog walker is hired and takes on the id that the original dog walker had. Bad news bears. Wrong information is given out.

Either scenario is a bad situation and can easily be prevented with this method: dependent: destroy

If the parent is destroyed the child records are destroyed. Good thing we are only talking about database records and not the cute forest characters of our impressionable childhoods!

All this boils down to referential integrity, or can I trust what you’re telling me. Since Ruby on Rails more often than not uses a relational database, this relational database concept is important to know and understand.

A post from thoughtbot summed up this concept better than I can so I will include that here:

“Referential integrity is a relational database concept that states implied relationships among data should be enforced. Referential integrity ensures that the relationship between rows in two tables will remain synchronized during all updates and deletes.”

In addition to using the dependent: destroy above, we also have another trick we can use.

*Note that the following trick is not usable with polymorphic associations since Rails maintains them, not the database. BUT, it is still cool to know about it (at least in my opinion which might mean nothing to you but I am going to continue explaining it anyway).

Foreign key constraints at the database level is another way to ensure referential integrity. What this does is ensure that a value in one table must match the values appearing in another table. With our example above, any time we try to point a dog to a dog walker that does not exist, it will fail. Basically, we cannot delete the walker if they still have dogs associated to them.

In the migration file, you can add something like this: add_foreign_key :dogs, :walkers, on_delete: :cascade

If a dog walker gets deleted, the dog records in the join table will be deleted as well.

With this in place, we no longer need the dependent: destroy method. The database will handle the deletion of associated records. Pretty neat trick!

Remember these things and you will help ensure that no record goes without a parent. Which I believe is a worthy cause and also a terrible campaign slogan.

If you’re looking for a team to help you discover the right thing to build and help you build it, get in touch.