# Rake migrate and creating join tables

**URL:** <https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504>\
**Category:** Rails\
**Created:** [December 17, 2005, 2:56am UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504 "2005-12-17T02:56:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jennyw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/7feea3/32.png) [@jennyw](https://www.ruby-forum.com/u/jennyw)\
**Post date:** [December 17, 2005, 2:56am UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504/1 "2005-12-17T02:56:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jennyw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/7feea3/32.png) [@jennyw](https://www.ruby-forum.com/u/jennyw)\
**Post date:** [December 18, 2005, 11:20pm UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504/2 "2005-12-18T23:20:32Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![jennyw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/7feea3/32.png) [@jennyw](https://www.ruby-forum.com/u/jennyw)\
**Post date:** [December 19, 2005, 4:15am UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504/3 "2005-12-19T04:15:02Z")

</div>

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

[Peak Obsession](http://api.rubyonrails.com/classes/ActiveRecord/Migration.html)

appears to be very limited.)

regards

Justin

---

<div class="post-metadata">

**Author:** ![jennyw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/7feea3/32.png) [@jennyw](https://www.ruby-forum.com/u/jennyw)\
**Post date:** [December 19, 2005, 4:21am UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504/4 "2005-12-19T04:21:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jennyw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/7feea3/32.png) [@jennyw](https://www.ruby-forum.com/u/jennyw)\
**Post date:** [December 19, 2005, 4:18am UTC](https://www.ruby-forum.com/t/rake-migrate-and-creating-join-tables/50504/5 "2005-12-19T04:18:03Z")

</div>

> 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](http://techno-weenie.net)