# RoR : Linking tables without ID column

**URL:** <https://www.ruby-forum.com/t/ror-linking-tables-without-id-column/69908>\
**Category:** Ruby\
**Created:** [September 25, 2006, 2:42pm UTC](https://www.ruby-forum.com/t/ror-linking-tables-without-id-column/69908 "2006-09-25T14:42:12Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Philip\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/c5a1d2/32.png) [@Philip\_N](https://www.ruby-forum.com/u/Philip_N)\
**Post date:** [September 25, 2006, 2:42pm UTC](https://www.ruby-forum.com/t/ror-linking-tables-without-id-column/69908/1 "2006-09-25T14:42:12Z")

</div>

Folks,

I need to link two tables together which don’t conform to the standard  
Rails  
conventions (it doesn’t have an ID column as the primary key / foreign  
key  
column). Â I’ve got everything working for the COUNTRIES table (primary  
key  
COUNTRY\_CODE), but can’t get the AIRPORTS table LIST function working  
(see  
error at bottom).

Help please.

Phil

Here are the DB definitions (for migration) -

============== START OF DB DEFNS ===============================  
class CreateCountries \< ActiveRecord::Migration  
 def self.up  
  create\_table(:countries, :id =\> false, :options =\> ’ IN SFPS001’) do  
|t|  
   # t.column :name, :string  
   t.column :country\_code, :string, :null =\> false, :limit =\> 2  
   t.column :country\_name, :string, :null =\> false, :limit =\> 50  
  end  
 end

 def self.down  
  drop\_table :countries  
 end  
end

class CreateAirports \< ActiveRecord::Migration  
 def self.up  
  create\_table(:airports, :options =\> ‘IN SFPS002’) do |t|  
   # t.column :name, :string  
   t.column :icao\_code  , :string, :null =\> false, :limit =\> 4  
   t.column :iata\_code  , :string, :null =\> false, :limit =\> 3  
   t.column :town     , :string, :null =\> false, :limit =\> 100  
   t.column :state    , :string,         :limit =\> 100  
   t.column :airport\_name, :string,         :limit =\> 100  
   t.column :country\_code, :string,         :limit =\> 2  
   t.column :latitude   , :float     
   t.column :longitude  , :float  
  end

  execute “alter table dbfps001.airports  
       add constraint r002001a foreign key  
       (country\_code)  
       references dbfps001.countries  
       (country\_code)  
       on delete restrict”  
 end

#  def self.down   drop\_table :airports  end end ==================== END OF DB DEFNS

Here are the models I’ve defined -

# ==================== START OF MODELS

class Country \< ActiveRecord::Base

 primary\_key :country\_code  
 has\_many :trip\_leg\_passengers, :as =\> :passport\_country  
 has\_many :airports, :as =\> :country\_code  
  
 validates\_presence\_of :country\_code, :country\_name  
 validates\_uniqueness\_of :country\_code  
 validates\_uniqueness\_of :country\_name  
end

class Airport \< ActiveRecord::Base

 has\_many :trip\_legs, :as =\> :start\_airport  
 has\_many :trip\_legs, :as =\> :end\_airport  
 belongs\_to :country, :foreign\_key =\> :country\_code  
  
 validates\_presence\_of :icao\_code, :iata\_code, :town  
 validates\_uniqueness\_of :icao\_code  
 validates\_uniqueness\_of :iata\_code  
end  
=================== END OF MODELS ================================

And here is the controller for Airports -

================== START OF CONTROLLERS ==========================  
class AirportController \< ApplicationController  
 #  
 # Enforce login  
 #  
 before\_filter :authorize, :except =\> :login

 layout “admin”

 def index  
  list  
  render :action =\> ‘list’  
 end

 # GETs should be safe (see  
[URIs, Addressability, and the use of HTTP GET and POST](http://www.w3.org/2001/tag/doc/whenToUseGet.html))  
 verify :method =\> :post, :only =\> [:destroy, :create, :update],  
     :redirect\_to =\> { :action =\> :list }

 def list  
  @airports = Airport.find(:all, :include =\> :country)

 end

 def show  
  @airport = Airport.find(params[:id])  
 end

 def new  
  @airport = Airport.new  
 end

 def create  
  @airport = Airport.new(params[:airport])  
  if @airport.save  
   flash[:notice] = ‘Airport was successfully created.’  
   redirect\_to :action =\> ‘list’  
  else  
   render :action =\> ‘new’  
  end  
 end

 def edit  
  @airport = Airport.find(params[:id], :include =\> :country)  
 end

 def update  
  @airport = Airport.find(params[:id], :include =\> :country)  
  if @airport.update\_attributes(params[:airport])  
   flash[:notice] = ‘Airport was successfully updated.’  
   redirect\_to :action =\> ‘show’, :id =\> @airport  
  else  
   render :action =\> ‘edit’  
  end  
 end

 def destroy  
  Airport.find(params[:id]).destroy  
  redirect\_to :action =\> ‘list’  
 end  
end

================== END OF CONTROLLERS ============================

When I try to list the airport records I get -

============== START OF CUT =============================  
ActiveRecord::StatementInvalid: [IBM][CLI Driver][DB2/LINUX] SQL0206N Â  
“COUNTRIES.ID” is not valid in the context where it is used.  
 SQLSTATE=42703  
SQLCODE=-206: SELECT airports.id AS t0\_r0, airports.icao\_code AS t0\_r1,  
airports.iata\_code AS t0\_r2, airports.town AS t0\_r3, airports.state AS  
t0\_r4,  
airports.airport\_name AS t0\_r5, airports.country\_code AS t0\_r6,  
airports.latitude AS t0\_r7, airports.longitude AS t0\_r8, countries.id AS  
t1\_r0, countries.country\_code AS t1\_r1, countries.country\_name AS t1\_r2  
FROM  
airports  LEFT OUTER JOIN countries ON countries.id =  
airports.country\_code  
=============== END OF CUT ==============================

In other words it is trying to join looking for an ID column in  
COUNTRIES.

Here’s the trace -

=============== START OF CUT ============================  
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active\_record/connection\_adapters/abstract\_adapter.rb:120:in  
`log' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/ibm_db2_adapter.rb:517:in `execute’  
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active\_record/connection\_adapters/ibm\_db2\_adapter.rb:466:in  
`select_all' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations.rb:1110:in `select\_all\_rows’  
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active\_record/associations.rb:975:in  
`find_with_associations' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations.rb:973:in `find\_with\_associations’  
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active\_record/base.rb:923:in  
`find_every' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:381:in `find’  
app/controllers/airport\_controller.rb:19:in `list' app/controllers/airport_controller.rb:10:in `index’  
=============== END OF CUT ================================