Have Some (Referential) Integrity with Foreign Keys

Derek Prior

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.

Rails allows us to easily set up these implied relationships, but does nothing to help us enforce referential integrity. It’s very simple to accidentally or intentionally break referential integrity in most Rails applications.

An Example Scenario

Consider the following minimal set of models describing a blogging engine:

class User < ActiveRecord::Base
  has_many :posts
  validates :name, presence: true
end

class Post < ActiveRecord::Base
  belongs_to :user
  validates :user, presence: true
end

Our blogging platform has taken off, but we’ve received requests from some users to delete their accounts. We add an interface for administrators to delete users and everything works fine.

A few days later we receive a report that we’re getting 500s on our “Popular Posts” page. Looking into it, we find that we’re getting:

undefined method &#96;name' for nil:NilClass

This is happening when we render the name of the user associated with each post. Somehow we’ve got a post that has no associated user even though Post has a validation that requires a user.

We quickly realize that we allowed administrators to delete users but never cleaned up the deleted users’ posts. We manually clean the data and make the following change to our model to prevent this in the future:

class User < ActiveRecord::Base
  has_many :posts, dependent: :destroy
end

The addition of dependent: :destroy means when a user is destroyed their posts will be as well. Administrators can now delete users without fear of orphaned posts causing problems.

Months pass and our now-venture-backed blogging engine has attracted millions of users. Unfortunately, lots of those users are spammers. We’re told we’ll be given a daily list of user_ids corresponding to spammers and need to write a job to delete them. We know this list could include thousands of ids on any given day, so we write the following code to avoid instantiating those objects and issuing thousands of queries to destroy them:

user_ids = CSV.read(csv_path).flatten
User.where(id: user_ids).delete_all

We soon receive a call telling us we’re getting 500s on the “Recent Posts” page. You guessed it; we violated referential integrity once again and we’re seeing the same NoMethodError as before.

Why didn’t dependent: :destroy save us here? Well, delete_all doesn’t instantiate the objects it is deleting and thus does not fire any after_destroy callbacks. The dependent options work via that callback.

Add Foreign Key Constraints

Fool me once, shame on me. Fool me thrice and I gotta find a new job. We can’t let this happen again. Rails can’t be trusted to maintain referential integrity, but you know what’s really good at doing that? Our relational database.

We can add foreign key constraints at the database level and ensure that the database will reject any operation that would violate referential integrity. Until Rails 4.2 ships with native support for foreign keys, we’ll need to add the Foreigner gem in order to do this. We add Foreigner and run the following migration:

def change
  add_foreign_key :posts, :users
end

This will run the following SQL if you’re using Postgres and Foreigner:

ALTER TABLE `posts`
ADD CONSTRAINT `posts_user_id_fk`
FOREIGN KEY (`user_id`) REFERENCES `users`(id);

With the foreign key in place, any operation that causes a post to point to a non-existent user will fail. It’s important to realize that a user_id of NULL is allowed, so we still need appropriate presence validations and NOT NULL constraints.

Now our nightly job is failing due to the foreign key constraint. The database is preventing us from deleting any users that still have associated posts. Does this mean we have to go back to the dreaded N+1 query scenario to destroy individual users?

Cascading Deletes

With a slight tweak to our foreign key, we can have the database, rather than ActiveRecord callbacks, handle the cascading deletes. Let’s change our foreign key just a bit:

def change
  remove_foreign_key :posts, :users
  add_foreign_key :posts, :users, dependent: :delete

  # or in the upcoming native support in Rails 4.2
  # add_foreign_key :posts, :users, on_delete: :cascade
end

This will run the following SQL when creating the foreign key:

ALTER TABLE `posts`
ADD CONSTRAINT `posts_user_id_fk`
FOREIGN KEY (`user_id`) REFERENCES `users`(id)
ON DELETE CASCADE;

With the dependent option functionality now moved to our foreign key, the database can now handle cleaning up the associated records. We no longer need to rely on callbacks for this behavior, so let’s remove the option.

class User
  # Old association:
  # has_many :posts, dependent: :destroy

  has_many :posts
end

Foreigner and the native support in Rails 4.2 both support options that cascade, nullify, and restrict changes. See the documentation for Foreigner and Rails 4.2.

Adding Foreign Key Constraints to an Existing Application

With immigrant, you can automatically generate a migration that will add any foreign key constraints your application is missing. With immigrant added to your Gemfile, run rails generate immigration add_foreign_keys to create the migration.

If you’re working with an application of any substantial size that has been running for some time, you are very likely to encounter errors applying this migration to your production data. Foreign key constraints cannot be applied if they are not valid for all current data.

I suggest downloading a copy of your production data and trying to run the migration on that data to surface any issues you will have at deployment time. Once the data is fixed and your migration applied in production the actions that were causing the invalid data will result in errors, which you can then target for fixes.

Caveats

Polymorphic associations are maintained by Rails; the database knows nothing about them. Foreign key constraints cannot help you here so you must keep logic in your Rails application to try to maintain referential integrity.

The Lesson

Foreign key constraints help us maintain valid data and are yet another way of helping us to avoid unexpected nil values in our applications. It’s unrealistic to expect application logic alone to provide the same level of protection.

Enforcing referential integrity is another job relational databases are better prepared to handle than Rails application code. Be a Juke Box Hero and check out Foreigner (or Rails 4.2) today.