The Software Version of Bambi is Falsifying Your Data.
How to seek/destroy/avoid Orphan Records.
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:
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!
dog_walkers includes two fields called
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
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
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:
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.