Association not working when foreign key other than ID

Question

I have two tables:

1.advertiser_accounts

create table if not exists advertiser_accounts (
   advertiser_id int(11) not null,
   deal_type_id int(11) not null,
   primary key (advertiser_id),
   CONSTRAINT account_advertiser_refs FOREIGN KEY (advertiser_id) REFERENCES advertisers (ap_advertiser_id ),
  CONSTRAINT deal_types_refs FOREIGN KEY (deal_type_id) REFERENCES  deal_types (id)
);

2. advertisers

Advertiser(id: integer, ap_advertiser_id: integer)

In Model :

AdvertiserAccount

class AdvertiserAccount < ActiveRecord::Base
 belong_to :advertiser   
end

Advertiser

class Advertiser < ActiveRecord::Base
 has_one :advertiser_account
end

I want to map advertiser_id in advertiser_accounts with ap_advertiser_id in advertiser table

On Console : AdvertiserAccount.first.advertiser

AdvertiserAccount Load (0.2ms) SELECT advertiser_accounts.* FROM advertiser_accounts ORDER BY advertiser_accounts.advertiser_id ASC LIMIT 1 Advertiser Load (0.2ms) SELECT advertisers.* FROM advertisers WHERE advertisers.id = 153371 LIMIT 1

I am getting nil as a result

But,i want query like this:

AdvertiserAccount Load (0.2ms) SELECT advertiser_accounts.* FROM advertiser_accounts ORDER BY advertiser_accounts.advertiser_id ASC LIMIT 1 Advertiser Load (0.2ms) SELECT advertisers.* FROM advertisers WHERE advertisers.ap_advertiser_id = 153371 LIMIT 1

Do you have an index on advertiser.ap_advertiser_id? Also, in my opinion, you're probably going to be better off in the long run if you rename the referencing field to match the referenced one. Even if it is only you (and only ever going to be you) dealing with the schema; if you go a year or so without dealing with it, this could become (at very least) annoyingly misleading. — Uueerdo
To reference a field, that field must be indexed. As far as the rest of what I said, many people looking would assume advertiser_id would reference advertiser.id, not advertiser.ap_advertiser_id. If you come back to this project to make a minor tweak years from now, you are likely to make the same assumption; which could lead to non-obvious errors. (I am speaking in purely MySQL terms btw.) — Uueerdo

RockStar RockStar · Accepted Answer · 2015-07-21T16:24:27

Have you checked whether ap_advertiser_id has unique key constraint or primary key constraint if not do it.

You are breaking conventions over configuration principle so you have to explicitly specify primary key and foreign key in model.

Try with changing models to -

class AdvertiserAccount < ActiveRecord::Base
  belong_to :advertiser, :foreign_key => :ap_advertiser_id,  :primary_key => :advertiser_id   
end

class Advertiser < ActiveRecord::Base
   has_one :advertiser_account,  :primary_key => :ap_advertiser_id, :foreign_key => :advertiser_id
end

Association not working when foreign key other than ID

1 Answers