# Does HABTM support non "id" FKs?

**URL:** <https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593>\
**Category:** Rails\
**Created:** [June 16, 2006, 2:55pm UTC](https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593 "2006-06-16T14:55:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![emo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ecc23a/32.png) [@emo](https://www.ruby-forum.com/u/emo)\
**Post date:** [June 16, 2006, 2:55pm UTC](https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593/1 "2006-06-16T14:55:38Z")

</div>

Quick question. Say I have a geographical database with counties and zip  
codes where counties have and belong to many zip codes.

zip\_codes (id, zip\_code)  
counties (id, name)

When I create the association table, the Rails way says to do the  
following: counties\_zip\_codes (county\_id, zip\_code\_id).

However, given that zip\_codes.zip\_code is itself a candidate key, I  
would much prefer to do the following: counties\_zip\_codes (county\_id,  
zip\_code), as this removes the need to join with the 42K record  
zip\_codes table.

However, when I do the following in the my model:

class ZipCode \< Active…  
has\_and\_belongs\_to\_many :counties, :foreign\_key =\> “zip\_code”

class County \< Active…  
has\_and\_belongs\_to\_many :zip\_codes, :association\_foreign\_key =\>  
“zip\_code”

AR still uses ZipCode.id when I call county.zip\_codes \<\< some\_zip,  
instead of ZipCode.zipcode, which is the preferred behavior.

Am I doing something wrong or is AR ignoring me? To solve the problem i  
simply made the ID of the ZipCode equal to the ZipCode, but I would like  
to know what I’m doing wrong.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![emo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ecc23a/32.png) [@emo](https://www.ruby-forum.com/u/emo)\
**Post date:** [June 16, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593/2 "2006-06-16T16:57:23Z")

</div>

It’s not going to work because you only have the ids in your bridge  
table.  
(country\_id, zip\_code\_id)

The association of zip to country is done via the join table. Remove the  
:foreign\_key and :association\_foreign\_key from your model declaration

Then you should be able to do

@country = Country.find\_by\_name “United States”  
@zip\_codes = @country.zip\_codes  
for zip in @zip\_codes  
zip.zip\_code  
end

Add a new one like this:

@country.zip\_codes \<\< ZipCode.create :zip\_code =\> “55123”

I suggest reading up on HABTM a little more in the Agile book or look at  
the  
API docs. What you were doing is a little overkill.

I hope that helps you out! Good luck!

---

<div class="post-metadata">

**Author:** ![emo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ecc23a/32.png) [@emo](https://www.ruby-forum.com/u/emo)\
**Post date:** [June 16, 2006, 5:11pm UTC](https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593/3 "2006-06-16T17:11:26Z")

</div>

emo wrote:

> Quick question. Say I have a geographical database with counties and zip  
> codes where counties have and belong to many zip codes.
> 
> zip\_codes (id, zip\_code)  
> counties (id, name)
> 
> When I create the association table, the Rails way says to do the  
> following: counties\_zip\_codes (county\_id, zip\_code\_id).
> 
> However, given that zip\_codes.zip\_code is itself a candidate key, I  
> would much prefer to do the following: counties\_zip\_codes (county\_id,  
> zip\_code), as this removes the need to join with the 42K record  
> zip\_codes table.
> 
> However, when I do the following in the my model:
> 
> class ZipCode \< Active…  
> has\_and\_belongs\_to\_many :counties, :foreign\_key =\> “zip\_code”
> 
> class County \< Active…  
> has\_and\_belongs\_to\_many :zip\_codes, :association\_foreign\_key =\>  
> “zip\_code”
> 
> AR still uses ZipCode.id when I call county.zip\_codes \<\< some\_zip,  
> instead of ZipCode.zipcode, which is the preferred behavior.
> 
> Am I doing something wrong or is AR ignoring me? To solve the problem i  
> simply made the ID of the ZipCode equal to the ZipCode, but I would like  
> to know what I’m doing wrong.

I think AR is ignoring you. You can tell habtm to use a non-standard  
foreign key name in the join table, but it will still use the id of the  
ZipCode object to populate the table, which for your case is wrong. You  
should be able to use the :finder\_sql, :delete\_sql and :insert\_sql  
options on habtm to tell it to change how it uses the join table fields  
and which field of the ZipCode to use to populate the table.

Another approach would be to use a has\_many :through association. The  
table of your join model (call it a ZipMapping) could contain county\_id,  
zip\_code\_id, and zip\_code. It would then be easy to use the has\_many  
association in County to find all counties with a certain zip\_code, or  
all the zip\_codes in the county - no join with the zip\_codes table  
required. Maintaining the zip\_code\_id in the association lets you more  
easily manage things if you need to change zip codes (say you want to  
convert from 5 to 9 digit codes).

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![emo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ecc23a/32.png) [@emo](https://www.ruby-forum.com/u/emo)\
**Post date:** [June 18, 2006, 2:54pm UTC](https://www.ruby-forum.com/t/does-habtm-support-non-id-fks/63593/4 "2006-06-18T14:54:09Z")

</div>

Thanks, this was helpful.

I ended up making zip\_codes.id a non-auto incrementing field that holds  
the actual “zip code”. I see plusses and minuses to this approach:

On the plus side I never have to join with the 42,000 record zip\_codes  
table (which will increase in size with canadian postal codes)

On the neutral side I need strict foreign key relationships with cascade  
so that I can change zip\_codes when they change (which apparently they  
do).

Thanks for the help.

Josh S. wrote:

> emo wrote:
> 
> > Quick question. Say I have a geographical database with counties and zip  
> > codes where counties have and belong to many zip codes.
> > 
> > zip\_codes (id, zip\_code)  
> > counties (id, name)
> > 
> > When I create the association table, the Rails way says to do the  
> > following: counties\_zip\_codes (county\_id, zip\_code\_id).
> > 
> > However, given that zip\_codes.zip\_code is itself a candidate key, I  
> > would much prefer to do the following: counties\_zip\_codes (county\_id,  
> > zip\_code), as this removes the need to join with the 42K record  
> > zip\_codes table.
> > 
> > However, when I do the following in the my model:
> > 
> > class ZipCode \< Active…  
> > has\_and\_belongs\_to\_many :counties, :foreign\_key =\> “zip\_code”
> > 
> > class County \< Active…  
> > has\_and\_belongs\_to\_many :zip\_codes, :association\_foreign\_key =\>  
> > “zip\_code”
> > 
> > AR still uses ZipCode.id when I call county.zip\_codes \<\< some\_zip,  
> > instead of ZipCode.zipcode, which is the preferred behavior.
> > 
> > Am I doing something wrong or is AR ignoring me? To solve the problem i  
> > simply made the ID of the ZipCode equal to the ZipCode, but I would like  
> > to know what I’m doing wrong.
> 
> I think AR is ignoring you. You can tell habtm to use a non-standard  
> foreign key name in the join table, but it will still use the id of the  
> ZipCode object to populate the table, which for your case is wrong. You  
> should be able to use the :finder\_sql, :delete\_sql and :insert\_sql  
> options on habtm to tell it to change how it uses the join table fields  
> and which field of the ZipCode to use to populate the table.
> 
> Another approach would be to use a has\_many :through association. The  
> table of your join model (call it a ZipMapping) could contain county\_id,  
> zip\_code\_id, and zip\_code. It would then be easy to use the has\_many  
> association in County to find all counties with a certain zip\_code, or  
> all the zip\_codes in the county - no join with the zip\_codes table  
> required. Maintaining the zip\_code\_id in the association lets you more  
> easily manage things if you need to change zip codes (say you want to  
> convert from 5 to 9 digit codes).
> 
> –  
> Josh S.  
> [http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)
