---
title: Have Some (Referential) Integrity with Foreign Keys
teaser: 'What is referential integrity and why is it important to your Rails apps?
'
tags: web,rails,postgresql
author: Derek Prior
published_on: 2014-12-02
---
[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.
[Referential integrity]: http://en.wikipedia.org/wiki/Referential_integrity
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:
```ruby
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 `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:
```ruby
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.
[`dependent: :destroy`]: http://guides.rubyonrails.org/association_basics.html#dependent
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_id`s 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:
```ruby
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:
[foreign key constraints]: http://www.postgresql.org/docs/9.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[native support]: https://github.com/rails/rails/pull/15606
[Foreigner gem]: https://github.com/matthuhiggins/foreigner
```ruby
def change
add_foreign_key :posts, :users
end
```
This will run the following SQL if you're using Postgres and Foreigner:
```sql
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].
[`NOT NULL` constraints]: http://guides.rubyonrails.org/migrations.html#column-modifiers
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:
```ruby
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:
```sql
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.
```ruby
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][foreigner-docs] and [Rails 4.2][rails-docs].
[foreigner-docs]: http://www.rubydoc.info/gems/foreigner/foreigner/ConnectionAdapters/AbstractAdapter#add_foreign_key-instance_method
[rails-docs]: http://edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key
## 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.
[immigrant]: https://github.com/jenseng/immigrant
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.
[downloading a copy of your production data]:
https://github.com/thoughtbot/parity#usage
## 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][uniqueness] 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.
[uniqueness]: https://thoughtbot.com/blog/the-perils-of-uniqueness-validations
[Juke Box Hero]: https://www.youtube.com/watch?v=c7tzi8wkYgI#t=1m31s