All About Foreign Keys

The concept of “foreign keys” in the Rails world has quite the potential to confuse, because, depending on the context of use, the intended meaning might actually differ! In addition, you might find yourself wondering how things like “references”, “foreign keys”, “associations” and “joins” are different, and why you should use one or not use the other.

You’ve probably reached a point in your Rails life where you want to understand more about how ActiveRecord works with your database. As you’ve become familiar with the ActiveRecord API, you have built up an intuition about how things work, but you know that to build efficient and elegant web applications with Ruby and Rails, it will benefit you greatly to deepen your understanding.

Foreign Keys

A foreign key is a mechanism that you can use to associate one table to another. Typically, it takes the form of a column with the name <other_table_name>_id. Let’s say you have a User table and a Project table, and you’ve used a has_many on User, you’ll see that a statement like user.projects when translated into SQL by ActiveRecord will automatically use user_id as a foreign key. Check it out yourself and see, try:

user.projects.to_sql

in your console. You should see an SQL statement that is searching for all projects with a user_id corresponding to user‘s ID.

ActiveRecord relies on this column to figure out which projects of all in the projects table are “related” or “associated” to the given user.

You probably know that doing has_many is not enough. You will also have to add a column to the Project table with the name user_id. Your migration might look like:

add_column :projects, :user_id, :integer

Newer versions of ActiveRecord also provide the #references and #add_reference method, which take care of adding the foreign key column for you. So, instead of using the add_column method, you can use the add_reference method, like so:

add_reference :projects, :user

This will add a user_id column to the projects table. The advantage with using the reference method is that you can add an index and a database constraint on the foreign key painlessly as well.

Check out this doc and this doc for more info.

Referential Integrity

If you were wondering what I meant when I said ‘database constraint on the foreign key’, rest assured, because I’m going to cover what that means here.

It depends on who you’re talking to, but in many contexts, a foreign key in the Rails world is understood plainly as the column itself. Depending on your use case, this could be enough. Keep in mind though, that the problem with this approach is that when we only have a foreign key column, we have no way of ensuring that for a given value of user_id, a corresponding User record actually exists. This has the potential to lead to confusion down the line.

To know what I mean, fire up your console and try the following (assuming you have the same setup as above, and that we have one User in the system with ID = 1)

project = Project.create(name: 'hit song', user_id: 2)

You should be able to create the project, but when you do project.user, you’ll get back nil. AKA an orphaned project. You can solve this with a validates in your model to ensure uniqueness and/or presence of the user, but you might find yourself in a situation where you’re bypassing validation callbacks somehow (probably with a bulk operation), and yet again creating orphans.

What solves this problem, is getting the database to ensure that orphaned records are not created.

Enter the add_foreign_key method, which you’ll notice is sort of a second meaning for the term ‘foreign key’. Using this method in your migration tells the database (in the way only ActiveRecord knows how) to enforce referential integrity by adding a foreign key constraint.

This very good article from the robots at thoughtbot goes into many of the finer details of this concept, and why it’s important.

You’d use it like so:

add_column :projects, :user_id
add_foreign_key :projects, :users

This Rails doc gives some additional details.

If you’re using the add_reference method, you can pass in a boolean to indicate if a foreign key constraint is to be added, like so:

add_reference :projects, :user, foreign_key: true

The .joins method

If you came to Rails without a background in SQL (like me), you’ve probably wondered about the .joins method, how it is related to the concept of associations and foreign keys, and when you should use it.

The rule of thumb with the .joins method is that you need to use it when you need to query more than one table at the same time.

In other words, if the query you’re writing for a given table/model needs to reference attributes in another table (or more), you will need to invoke .joins to make this happen. This holds true regardless of if the tables are associated to each other with a foreign key column or constraint.

So for example, if we want to query the projects table for all projects that are owned by a user of role ‘manager’ (assuming we have a ‘role’ column on the users table), we’d do:

Project.joins(:user).where(user: { role: 'manager' })

You’ll notice that if you try this without the .joins, ActiveRecord will complain.

For some background on joins, check out this nice summary on the different types of joins you can do in SQL. You can even use the .joins method to query multiple tables that are not associated to one another if you so choose. I encourage you to fire up the console and use the .to_sql method for your queries whenever you’re stuck to get a better handle on what ActiveRecord is doing behind the scenes.

Hope this helped, and as always, please leave a comment below if you’re stuck on something that you need help with or if something above was unclear in any way. I’d love to help out!

6 thoughts on “All About Foreign Keys

  1. Another thing very important related to foreign keys is that they must have an index.

    As a rule of thumb, to make sure every time you need one, you also create an index use the $ rails g model AddSomeModel user:belongs_to

    That will create a migration where the foreign key has already index: true, foreign_key: true.

    Another powerful tip is to run the gem lol_dba 🙂 https://github.com/plentz/lol_dba/

  2. Great point about using add_foreign_key in addition to validates! I learned SQL before Ruby and/or Rails, so my old applications have no notion of database-side referential integrity. Thanks for the summary!

Leave a Reply

Your email address will not be published.