— Aug 06, 2013
Originally posted on the Big Nerd Ranch Blog.
Last time, we discussed how database null constraints and default values can increase confidence in your app’s data.
This time, I want to take a look at uniqueness constraints. Rails provides uniqueness validations, but as we learned in the previous post, validations aren’t necessarily the right tool to ensure data integrity. And Rails’ uniqueness validation in particular is not without its perils.
Eventually you’ll realize that correctness demands certain data be unique. One of the most common examples for a uniqueness constraint is for users. If you allowed multiple users to have the same email address or username, users would be indistinguishable. Such information serves as identification for individuals.This example has been driven into the ground, so let’s talk about something more interesting.
Consider an app that has teams and users. Say we want users to be a member of any number of teams. This is an example of a many-to-many relationship and will require a join table. Let’s call this table
We’ve intentionally left out the uniqueness constraint in this example to illustrate its pitfalls. Also, don’t miss the null constraints! These join records don’t make much sense without pointing in both directions.
Let’s we have the respective models set up as needed for such a relationship. Time to take them for a spin:
As you can see, without the uniqueness constraint we allow users to be a member of a team more than once. That doesn’t make much sense (I recently experienced this first hand). Now we’ll lock this table down by adding the constraint. In SQL, uniqueness is enfored by creating a “unique” index. This index may span multiple columns to ensure uniqueness with respect to multiple attributes:
That’s pretty much it! Now the database won’t allow multiple membership records to exist that reference the same team and user.
Note: You won’t be able to adjust the migration as I did in the example without rolling it back first.
As always, it is tricky to add constraints to existing tables because we are unable to assume the state of the data will allow such constraints to be added. For uniqueness we need to make sure the target column is unique before adding the constraint. Here is an idea to get your wheels turning:
Since we’re assuming there are no constraints on
foo we first need to make sure it is at least an empty string, so we coalesce its value to an empty string. Then we concatenate that value with the record’s
id since we know it as a unique value. The resulting value will always be unique to the record!
You may also be interested in my solution to the team membership problem mentioned above.
I hope that you now have an understanding of the uniqueness database constraint and why it can really help improve your app’s data integrity. There is one other constraint that was overlooked in the memberships example. That’ll be our next target: foreign keys.
Coding Rails with Data Integrity, Part 1 (null constraints and default values)
Coding Rails with Data Integrity, Part 2 (uniquness constraints)
What other ways have you come up with to ensure data integrity in your apps? We’d love to hear what you think!