Rails add foreign key on non-primary key

Question

In Rails 5.1 I have the following relations:

class RootArea < ApplicationRecord
  has_many :common_areas
end

class CommonArea < ApplicationRecord
  belongs_to :root_area, foreign_key: 'area_id', optional: true
end

and below are their migrations:

create_table "root_areas", force: :cascade do |t|
  t.integer "area_id"
  t.string "localname"
  t.string "globalname"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "common_areas", force: :cascade do |t|
  t.integer "area_id"
  t.string "localname"
  t.string "globalname"
  t.integer :root_area_id
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

My goal is to "connect" common_areas to root_areas -on area_id instead of just the id- so that a single root area entry may "have" multiple common areas.

For instance USA(RootArea) has California(CommonArea), Texas(CommonArea) etc.

I tried to do this using a migration and a foreign key as shown below:

add_foreign_key :common_areas, :root_areas, primary_key: :area_id

and while at first this seemed to work well, it eventually failed:

>>> RootArea.create!(area_id: 1111, 
                     localname: ’test', 
                     globalname: ’test') # OK

>>> CommonArea.create!(area_id: 9999, 
                       localname: ’test', 
                       globalname: ’test') # OK

>>> RootArea.first.common_areas << CommonArea.first # Error

=> # SQL (44.3ms)  UPDATE "common_areas" SET "root_area_id" = $1, "updated_at" = $2 WHERE "common_areas"."id" = $3  [["root_area_id", 19], ["updated_at", "2018-02-20 14:45:52.545450"], ["id", 1]]

The output suggests that Rails tries to set CommonArea's attribute root_area_id as RootArea's primary key (id instead of area_id).

For instance in the above example the generated sql statement should have set root_area_id to 1111 instead of 19.

Thanks. So you've no class called 'area'? It looks as if it would be an easier way to achieve what you want using `has_many ... through ` — rwold, Feb 20 '18 at 16:29
I don't quite understand why there are both `root_areas` and `common_areas` tables. Or why this isn't a HMT etc. — Dave Newton, Feb 20 '18 at 17:07
So, I'm trying to do the following: i.e. California `belongs_to` USA, Texas `belongs_to` USA etc. But I also need to be able to do USA.common_areas and spit out California, Texas etc. What's the best way to do that? — stratis, Feb 20 '18 at 19:25

score 7 · Accepted Answer · answered Feb 21 '18 at 07:52

Later this evening I seeked out some further help from the Rails IRC channel and eventually user dionysus69 pointed me to this post which was very similar to what I was looking for. For future reference this is the final solution:

class RootArea < ApplicationRecord
  has_many :common_areas, foreign_key: 'root_area_id', primary_key: 'area_id'
end

class CommonArea < ApplicationRecord
  belongs_to :root_area, foreign_key: 'root_area_id', primary_key: 'area_id', optional: true
end

Now I can successfully do

>>  RootArea.first.common_areas << CommonArea.first

and have root_area_id set to the proper area_id value instead of the id.

Rails add foreign key on non-primary key

1 Answers1