— 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.
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:
Unfortunately, this migration fails to guarantee referential integrity. That is records may become orphaned:
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 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:
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
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.
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.
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:
:dependent option tells the database to delete this record whenever the referenced record is 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!
Now the database will prevent us from deleting a team that has members.
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.
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!