— Aug 22, 2013

Originally posted on the Big Nerd Ranch Blog.

In our previous posts about data integrity in Rails, we covered  null constraints, default values and uniqueness constraints. These database constraints help ensure that data exists where it’s supposed to and in a form that makes sense for your domain model.

This time, I would like to take a look at referential integrity. We’ll find out how the database can be harnessed to ensure related records may trust one another under certain circumstances.

Gotta Support the Team

Disclaimer: Rails’ default database is SQLite, which doesn’t support foreign key constraints out of the box. In order to attempt the concepts in this post, try another SQL database such as PostgreSQL or MySQL.

In Coding Rails with Data Integrity, Part 2 I outlined a simple data model where Users may be members of Teams. This is done by way of the Membership join model. Constraints ensure that duplicate and incomplete memberships cannot exist in the database. The migration for memberships looks like this:

class CreateMemberships < ActiveRecord::Migration
  def change
    create_table :memberships do |t|
      t.belongs_to :team, null: false
      t.belongs_to :user, null: false
      t.index [:team_id, :user_id], unique: true
    end
  end
end

Unfortunately, this migration fails to guarantee referential integrity. That is records may become orphaned:

user = User.create!  # => #<User id: 1>
user.teams.create!   # => #<Team id: 1>
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
user.destroy
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]

Now there is an invalid membership in the database. We have data that relates a team to a user that no longer exists. Following that reference leads to nothing. This fills the database with useless records and may lead to 404 landmines when someone browses memberships.

The Rails Solution

The keen reader is probably thinking “You can do this stuff with Rails’ associations using the :dependent option.” Yes you can, and it may very well make sense for your app. You may do something like this:

class User < ActiveRecord::Base
  has_many :memberships, dependent: :destroy
  has_many :teams, through: :memberships
end

user = User.create!  # => #<User id: 1>
user.teams.create!   # => #<Team id: 1>
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
user.destroy
Membership.all       # => []
# Thanks, Rails! You did it!

Something that should be considered is that Rails has a couple of different ways to remove records from the database. A record may either be deleted or destroyed. Deletion skips callbacks, and since the :dependent option on Rails associations is implemented using callbacks, you could still orphan records by “deleting” them rather than “destroying” them.

user = User.create!  # => #<User id: 1>
user.teams.create!   # => #<Team id: 1>
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
user.delete
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
# Shazbot!

Foreign key constraints enforce referential integrity at the database level. This means referential integrity exists in spite of the application code. The win becomes obvious once you stop thinking of the database as this private slave of the Rails app and instead as an application-independent data-store. One could theoretically introduce another app that interacts with the same database without worry for the integrity of the data.

Enter Foreigner

Ruby on Rails omits foreign key constraints as a built-in feature, because databases have uneven support for them. The foreigner rubygem is a great library for adding foreign key constraints in your migrations. Below we’ll see foreigner in action.

So what do we want to happen to the Membership when a referenced User is deleted? In this case, it probably makes sense to just delete the membership, since it doesn’t mean anything without a user. We’ll add a to the member’s user reference:

class AddUserForeignKeyToMemberships < ActiveRecord::Migration
  def change
    add_foreign_key :memberships, :users, dependent: :delete
  end
end

The :dependent option tells the database to delete this record whenever the referenced record is deleted.

user = User.create!  # => #<User id: 1>
user.teams.create!   # => #<Team id: 1>
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
user.delete          # not "destroy" with all those fancy callbacks!
Membership.all       # => []
# Nice! The membership record was automatically deleted

How about the other side of the relationship? That is, what should happen when a referenced Team is deleted? That decision is probably left up to the domain of your app, but for example’s sake, let’s say we don’t want to allow a Team to be deleted if it has any users. Constrain it!

class AddTeamForeignKeyToMemberships < ActiveRecord::Migration
  def change
    add_foreign_key :memberships, :teams, dependent: :restrict
  end
end

Now the database will prevent us from deleting a team that has members.

team = Team.create!  # => #<Team id: 1>
team.users.create!   # => #<User id: 1>
Membership.all       # => [#<Membership id: 1, team_id: 1, user_id: 1>]
team.destroy         # => ActiveRecord::InvalidForeignKey raised!
# Aww, thanks database :)

If your app has foreign key constraints, declare them, and let the database do the dirty work!

I want to mention that there are other great libraries out there that allow adding constraints to your database. Rein is another good example that I haven’t used personally. In the end, always use the right tool for the job.

Other Posts

• Coding Rails with Data Integrity, Part 1 (null constraints and default values)

• Coding Rails with Data Integrity, Part 2 (uniqueness constraints)

What other ways have you come up with to ensure data integrity in your apps? We’d love to hear what you think!