Technology and Software

Ruby on Rails, why foreign keys matter

Foreign keys don’t belong to the Rails way but I keep adding them to any Rails project I work in. Sometimes it’s not easy to convince the other developers that they are useful but I’ve run into an nice example yesterday, right on the first day we added foreign keys to a project.

It’s a project we inherited from another development team. We had two models in a N:N relationship (has_and_belongs_to_many) and a join table. We’re still learning what the code does and how it is organized so we didn’t realized that the join table has an ActiveRecord model too. Actually that’s unusual but it is also the way to go to have attributes on the relationship (Rails calls it a join model). Let’s say those models are Post, Author and the join table is authors_posts.

So, without paying attention to the real meaning of that table we grepped _id in the migration files and semi-automatically created a migration that added the foreign keys to all the tables. We had to inspect some tables to be sure of what we we’re doing but we didn’t pay attention to authors_posts. By the way, did I mention we are under time pressure and the previous developers didn’t properly cover the code with tests? Ok, you get the picture.

So, we’ve got our foreign keys. The few tests we have pass and the application keeps running. Good. That was the morning. A developer deleted a Post object  in the afternoon and got an error: the database complained that deleting a record from posts would break a foreign key in authors_posts. Strange… We started to look carefully at the code and realized that they used the authors_posts table (actually, it was named in the wrong order, posts_authors, that should have told us something was wrong) to manually store the association without letting ActiveRecord do it. They had has_many :authors_posts in the two models.

We spent time to fix that through all the code and the database (attribute names…). If it were not for the foreign keys:

  1. the database would be soon full of author_posts records about deleted authors and posts (remember those are not the two models we’re using)
  2. we would eventually find the bug in some more painful way, with more pressure to fix it, more code to fix, more data to change

Summing up, foreign keys are a kind of firewall in front of the data: you don’t run your network without a firewall, you don’t run your database without foreign keys. They catch all the bugs that escape your tests and the flaws in your design.


PS: to delete a object in that kind of association follow the instructions in this post. In short: post.authors.clear (which deletes the association) and post.delete