What is a Foreign Key?

Foreign keys, if you haven’t come across them before, can be a confusing idea. To add to the confusion, there are many inter-related concepts and terminology like “references”, “associations” and “joins” that go along with the idea of foreign keys. What is a foreign key and when should you use one?

A crucial piece of technology that most web applications rely on is the relational database (like Postgres, MySQL etc). The relational database both models the external world for our application and stores important information about the external world that our application logic uses. Foreign keys are a relational database concept and play an imporant role in defining the relationships in a relational database.

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.