How To Add A Foreign Key in Ruby on Rails

This article will walk through how to create a simple application that has two databases that relate to each other using a foreign key. Unlike formal documentation, my hope here is that by demonstrating the actual uses in migrations, models, controllers, and views, it will be easier for others to implement this feature. 

 

What is a foreign key? 

A foreign key is a field in one database that uniquely identifies a row in another table. For this example, our foreign key in the expenses table will identify which category each expense should fall under. Still confused? Ok, maybe an example will help. 

For example, here we'll build two tables, one for expenses and one for categories of those expenses. Here, a foreign key of 1 in the category_id column will relate to food expenses, a foreign key of 2 will relate to accommodation expenses, and so forth. Let's dive in. 

 

Generate Models 

To start off, I created a new rails application and established the primary database, expenses. At first I just generated a model, but in the hopes of making this tutorial a little simpler, let's use a scaffold. This will help us simplify the controller and view process later on. 

rails new foreign_key <br>
rails g scaffold expense title:string amount:decimal <br>
rake db:migrate

Here, I'm using the decimal data type instead of integer for amount. This enables the app to handle expenses that include cents instead of only round numbers. 

Next, I'll generate another model, this one for the categories of our expenses. After theses commands, we'll now have two separate tables. However, they won't know that the other exists yet. 

rails g model category name:string
rake db:migrate

 

Migration

The following migration, if followed exactly (or with the model names replaced with your respective model names) will create a new column in the Expenses table named category_id. 

rails g migration add_category_id_to_expenses category_id:integer
rake db:migrate

Special note: If the column in the database already exists, you can create a migration file named anything and then add a foreign key with the special add_foreign_key command like so: 

class AnyMigration < ActiveRecord::Migration
  def change
    add_foreign_key :expenses, :categories
  end
end

At this point, it's best practice to head over to your schema.rb file and make sure that the Expenses table has a column named category_id. If not, try troubleshooting the commands above.  

 

Setup the Relationship

Now we'll need to add the relationships details in our model and ensure that categories has the correct corresponding database tables to handle our expense types. In app/models/expense.rb, we'll need to add a belongs_to method. Each expense should belong to a category. 

class Expense < ActiveRecord::Base
  belongs_to :category
end

Additionally, it's important at this point that we add some example categories so that our expenses can be grouped. To do that, I'll dive into the Rails Console and create some new instances of the Category object. The first command will have to be Category.connection, then we can get started using the syntax of: Category.create(name:"Example").

 

Controller & Views

Now that we've got our models set up and the relationship between the two established, let's work towards implementing this feature by moving next to the controller. In this example, I'll be using a controller only for Expenses, not for Categories. The scaffold should have already set up the basic methods and functionality, so we'll just have to make a few adjustments. 

First, we must enable category_id as an accepted parameter for an expense by adding it to the expense_params method. 

 def expense_params
    params.require(:expense).permit(:title, :amount, :category_id)
  end

At this point, our database logic should actually already work. By going into the rails console, I was able to create a new instance of Expense that has a category_id.

However, to make our logic render in the view, we'll need to add a form. My favorite way to do so is with the simple_form gem, which is devastatingly easy to set up. Just follow the three commands in the Installation section of their Github repo. 

With simple form up and running, the view can be pared down to just this. 

Here we're using the collecton_select helper to grab the names of all the Categories, rendering the submission as a category_id within Expenses. Success! At this point, we've established a model, controller, and view that all work together to enable the Expense table to accept different Categories. But let's take it one step further...

 

Extra Credit

In the project I was working on, I wanted to have an Expenses#Index page where a user can filter through different expense categories just by clicking on the category name. To make this happen, first add a link_to for each category in app/views/layouts/application.html.erb. Add this loop after the body tag but before the yield tag. 

<% Category.all.each do |category| %> 
        <%= link_to category.name, expenses_path(category: category.name) %>
<% end %>

Finally, we need to pass some logic through the controller that will tell the app which category to display. 

What's going on here? In the index method, we'll start by sending through a category hash. If it doesn't contain any parameters, we'll display all the expenses, ordered by when they were created. 

But if the hash has a specific id, we'll filter to show only that category_id, displayed as the category name. We can test this in the browser, noting that the parameter is visible in the URL after clicking on one of the filters (i.e. http://localhost:3000/expenses?category=Food). 

Questions? Something not working? Check out the Github repo or let me know in the comments below. Happy coding!