How to keep your data consistent with foreign key constraints

We all have that co-worker (and have been that co-worker) who SSHs into a server and runs SQL statements against live data. On staging servers this can be a minor issue if things go wrong, but in production it can be disastrous. At WillCall we often have to spend time putting our staging data into different configurations for testing purposes and have felt the pain of inconsistent data more than once.

Today we’re going to talk about data consistency, why keeping your data consistent is always a challenge, and what you can do about it.

Integrity issues can creep in unintentionally if you do not have a deep understanding of how ActiveRecord callbacks and validations work and which methods skip them entirely!

For example if you have a Post model and each Post had many Comments, when you call post.destroy with the dependent: :destroy option, it will destroy the associated comments as well.

class Post < ActiveRecord::Base
  has_many :comments, dependent: :destroy
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

This keeps your data consistent. You have no orphaned comments floating around waiting to explode when you call comment.post.title.

However if someone were to mistakenly use post.delete instead of post.destroy the callbacks that destroy the associated comments would never be run!

The Rails guides have a list of dangerous methods that skip callbacks for various reasons and should probably be avoided by junior most developers. They include:

• decrement
• decrement_counter
• delete
• delete_all
• increment
• increment_counter
• toggle
• touch
• update_column
• update_columns
• update_all
• update_counters

Solution: Foreign key constraints.

Foreign key constraints are the database’s solution to this data integrity problem. They are basically a way to tell the database to only allow actual Post IDs in the comments post_id column. This means if you tried to add a comment to a non-existent or deleted post, or delete a post that has comments, it would raise an ActiveRecord::InvalidForeignKey exception.

Rails does not have support for foreign key constraints because DHH is not a fan of them, but there are several gems that will add this ability for you. Foreigner is the most popular, but I have been having good results with the more full featured SchemaPlus. I’ll show how to use both:

Foreigner

Foreigner is a simple gem that just adds methods to create foreign keys in mysql, postgres, and sqlite. To install Foreigner, add the following to your Gemfile:

gem 'foreigner', '~> 1.6.1'

And then install with $ bundle install.

Now you can use the foreign_key method in your migrations and the foreign key constraint will be added for you.

create_table :comments do |t|
  t.string :body
  t.foreign_key :posts
end

And that’s it! You can also add null: false to ensure that the post_id is not nil.

SchemaPlus

SchemaPlus is another option. Although being slightly less popular it adds some conveniences that foreigner does not like column default expressions which I might talk about in another post. Another benefit is that it will automatically add foreign key constraints to all t.references and t.belongs_to methods in your migrations for you. To install schema_plus add the following to your Gemfile:

gem 'schema_plus', '~> 1.4.1'

And then install with $ bundle install. And you’re done! When you start your next project this is definitely something I would try playing with. There are also many options you can pass that customize your foreign key constraints described here.

Testing Integrity

Finally adding a few tests is always a good thing, so if you want to test that you cannot accidentally violate your data integrity you could do something like this in rspec.

require 'spec_helper'

describe 'Post Integrity' do
  it 'should not allow posts to be destroyed if they have comments' do
    post = create(:post)
    create(:comment, post: post)
    expect { post.destroy! }.to raise_error(ActiveRecord::DeleteRestrictionError)
  end

  it 'should not allow posts to be deleted if they have comments' do
    post = create(:post)
    create(:comment, post: post)
    expect { post.delete }.to raise_error(ActiveRecord::InvalidForeignKey)
  end
end