Forum: Ruby on Rails Rake migrate and creating join tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
jennyw (Guest)
on 2005-12-17 03:56
(Received via mailing list)
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? ;-)

Thanks!

Jen
Duane J. (Guest)
on 2005-12-19 00:20
(Received via mailing list)
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)
Justin F. (Guest)
on 2005-12-19 05:15
(Received via mailing list)
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? ;-)

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/Mi...

appears to be very limited.)

regards

   Justin
Rick O. (Guest)
on 2005-12-19 05:18
(Received via mailing list)
>
> 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
Justin F. (Guest)
on 2005-12-19 05:21
(Received via mailing list)
Justin F. wrote:
[...]
>
> appears to be very limited.)

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

<quote>
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
</quote>

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

regards

   Justin
This topic is locked and can not be replied to.