Rake migrate and creating join tables


#1

Just wondering if there’s a way to use migrations to generate join
tables (where the table columns comprise only of foreign keys to other
tables, and where the primary key is all the foreign keys taken
together).

I made the changes manually in MySQL, then did rake db_schema_dump and
it gave something like this:

create_table "apples_oranges", :id => false, :force => true do |t|
  t.column "apple_id", :integer, :limit => 10, :null => false
  t.column "orange_id", :integer, :limit => 10, :default => 0, :null

=> false
end

The problem is that when you run this code, there’s no primary key
generated on the table. I’ve looked through the source code, and noticed
that the code doesn’t even attempt to create a primary key unless :id =>
true, so I tried:

create_table "apples_oranges", :primary_key => "apple_id,

orange_id", :force => true do |t|

But that didn’t work – it tried to create a column called “apple_id,
orange_id” – not at all what I wanted. I also tried the :primary_key
type for the columns, but that didn’t work, either (not that I expected
it to, since I’m pretty sure that creates autoinc columns).

Looking at the source code some more, I’m pretty sure at this point it
just can’t be done. But I thought I’d ask anyway, hoping to be proved
wrong. So, am I wrong? :wink:

Thanks!

Jen


#2

On Dec 16, 2005, at 6:55 PM, jennyw wrote:

 t.column "orange_id", :integer, :limit => 10, :default =>  

0, :null => false
end

Just take the :id out altogether:

create_table "apples_oranges", :force => true do |t|
  t.column "apple_id", :integer, :limit => 10, :null => false
  t.column "orange_id", :integer, :limit => 10, :default =>

0, :null => false
end

The ‘create_table’ method adds an “id” column of type :integer by
default.

Duane J.
(canadaduane)


#3

jennyw wrote:

=> false
But that didn’t work – it tried to create a column called “apple_id,
orange_id” – not at all what I wanted. I also tried the :primary_key
type for the columns, but that didn’t work, either (not that I expected
it to, since I’m pretty sure that creates autoinc columns).

Looking at the source code some more, I’m pretty sure at this point it
just can’t be done. But I thought I’d ask anyway, hoping to be proved
wrong. So, am I wrong? :wink:

Can you execute arbitrary SQL in a migration?

The DML functionality in ActiveRecord has always allowed this, so I
would have expected the DDL functionality in migrations to do the same.

It might not be database-independent, but it would escape from the
limitations of the current migrations mini-language (which, from the
documentation at

http://api.rubyonrails.com/classes/ActiveRecord/Migration.html

appears to be very limited.)

regards

Justin


#4

Justin F. wrote:
[…]

appears to be very limited.)

If I had scrolled down a bit in the documentation, I would have found
the answer:

And sometimes you need to do something in SQL not abstracted directly by migrations:

class MakeJoinUnique < ActiveRecord::Migration
def self.up
execute “ALTER TABLE pages_linked_pages ADD UNIQUE
page_id_linked_page_id (page_id,linked_page_id)”
end

 def self.down
   execute "ALTER TABLE `pages_linked_pages` DROP INDEX

page_id_linked_page_id"
end
end

So you should be able to use this to add your primary key constraint.

regards

Justin


#5

appears to be very limited.)

execute ‘arbitrary sql’

You can also pass options to create_table:

create_table :foos_bars, :id => false, :options => ‘blah blah’ do |t|

end

It will add the :options to the end:

CREATE TABLE foos_bars (
) :options;


rick
http://techno-weenie.net