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
On Dec 16, 2005, at 6:55 PM, jennyw wrote:> 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 >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 Johnson (canadaduane)
jennyw wrote:> 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? ;-)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
> 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.) >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 Forder wrote: [...]> 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.)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