Matthew Lindfield Seager

Matthew Lindfield Seager

Rails, Foreign Keys and Troubleshooting

Rails’ foreign_key confuses me sometimes! I spent way too long yesterday trying to troubleshoot why a Rails relationship was only working in one direction while I was overriding the class so this post is my attempt to explain it to someone else (probably future me) in order to make sure I understand it.


Imagine we’re trying to model the results of a running race. One way to structure it might be to have a result that belongs_to both a race and an athlete… or that a race has_many results and so does an athlete.

Further imagine that our hypothetical app already has a ‘Person’ object (and a ‘people’ table). Unless the running race is between thoroughbreds (or camels or turtles, it’s likely that our athletes are also people.

Rather than store people twice, once in an athletes table and once in a people table, lets reuse the existing table:

Data model showing a one to many relationship from Race to Result and from Person to Result


Generate models

The first step is to generate the new model objects. As per the requirements the ‘Person’ model already exists so we only need ‘Race’ and ‘Result’

rails g model Race   date:date distance:integer
rails g model Result race:references athlete:references time:integer

By using :references as the column type in the generator, the ‘CreateResults’ migration is set up to automatically create a ‘race_id’ column (and an ‘athlete_id’ column) of the right type as well as setting null: false and foreign_key: true for both.

Migrate the Database

That’s all we need to do for races but if we try to run the migrations now with rails db:migrate the second one (‘CreateResults’) will fail. The error returned by Postgres is PG::UndefinedTable: ERROR: relation "athletes" does not exist.

This makes sense; after all there is no ‘athletes’ table. The solution is to tell ActiveRecord to use the ‘people’ table for the foreign key. This is acheived with a relatively poorly documented API option that I only found (indirectly) through Stack Overflow

After changing our create table migration from:
t.references :athlete, null: false, foreign_key: true
t.references :athlete, null: false, foreign_key: { to_table: :people }
we can now successfully finish our migrations.

Add Associations

We’re getting closer now but our associations still need work. ‘Result’ kind of knows what it belongs to thanks to the generator but it does need a little help to know what class of athlete we’re dealing with here:

class Result < ApplicationRecord
  belongs_to :race
  belongs_to :athlete, class_name: 'Person'
  # add this bit:    ^^^^^^^^^^^^^^^^^^^^^^

Originally I specified the option foreign_key: 'athlete_id' too but this is unnecessary. The foreign key defaults to the association name (i.e. ‘athlete’) followed by ‘_id’ so specifying it adds nothing.

With ‘Result’ belonging to ‘Person’ (via ‘athlete_id’) and ‘Race’, now we just need to let them both know they have_many results:

# In race.rb add:
  has_many :results

# In person.rb add:
  has_many :results, foreign_key: 'athlete_id'
  #                ^^^^^^^^^^^^^^^^^^^^^^^^^^^

The bit I’ve “underlined” is the key (excuse the pun 🙄). As I said earlier, originally I was specifying that exact option but on the belongs_to side, up until I finally figured out it was doing nothing over there.

The reason it belongs over on the has_many is that when we call the #results method on a Person object, the object already knows its own ID. Let’s say’s it an object called ‘matthew’ with an ID of 123. When we call matthew.results without the foreign key specified Active Record translates that to the following SQL (slightly simplified):
SELECT * FROM "results" WHERE "person_id" = 123

Without the foreign key specified, Active Record assumes that the column in the ‘results’ table will be called ‘person_id’. In our situation that causes a PG::UndefinedColumn: ERROR: column results.person_id does not exist error when we call matthew.results.

With the foreign key, Active Record knows which column in the ‘results’ table to search when looking for the id of a person:
SELECT * FROM "results" WHERE "athlete_id" = 123

It was a little bit (lot!) counter-intuitive to me that I had to tell ‘Person’ what column to use when querying another table but now I’ve written it all down it’s finally making sense to me!

But What About inverse_of?

Another thing I tried during all my troubleshooting was setting the inverse_of option on the has_many side:

# person.rb
has_many :results, inverse_of: 'athlete'

I was thinking that would help it to infer the correct column name from the class_name option on the belongs_to relation. However, I was still getting the same error I mentioned earlier:
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column results.person_id does not exist)

After reading a little more (especially this helpful article from Viget) my understanding now is that:

  • inverse_of can help prevent unnecessary database calls (e.g. if the person is already in memory when I call result.athlete)
  • it’s not necessary to specify inverse_of on simple relationships (e.g. between ‘Race’ and ‘Results’)
  • if you want the aforementioned benefits, it is necessary to specify inverse_of when we provide custom association options such as class_name and foreign_key (i.e. like we did in our solution)

So with all that said, the final configuration I settled on was:

# app/models/person.rb
class Person < ApplicationRecord
  has_many :results, foreign_key: 'athlete_id', inverse_of: :athlete

# app/models/race.rb
class Race < ApplicationRecord
  has_many :results

# app/models/result.rb
class Result < ApplicationRecord
  belongs_to :athlete, class_name: 'Person', inverse_of: :results
  belongs_to :race

Final Take-away

As I was finishing up this post and trying a few things I again encountered some strange behaviour. I briefly started to have flashbacks about my hour and a half of troubleshooting and banging my head against a wall yesterday but thankfully, with my newfound understanding of how it all actually works, I was able to detach myself from the situation.

It seems that calling reload! in the Rails console wasn’t actually causing my models to be reloaded… after stopping and starting the console everything worked correctly. I’m almost certain I tried the correct configuration at some point yesterday so now I have a very strong suspicion that the same thing was happening then; that after adding in the foreign key on the has many side my attempts to reload! were unsuccessful.

It seems like there’s more I need to learn about the console and how reloading works but, considering I’m over a 1,000 words in at this point, that’s an investigation for another day!