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)

• Coding Rails with Data Integrity, Part 3 (foreign key constraints)

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