# Migrating a column to foreign key and foreign table

**URL:** <https://www.ruby-forum.com/t/migrating-a-column-to-foreign-key-and-foreign-table/172849>\
**Category:** Rails\
**Created:** [July 28, 2009, 11:43pm UTC](https://www.ruby-forum.com/t/migrating-a-column-to-foreign-key-and-foreign-table/172849 "2009-07-28T23:43:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ate50eggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/fbc32d/32.png) [@ate50eggs](https://www.ruby-forum.com/u/ate50eggs)\
**Post date:** [July 28, 2009, 11:43pm UTC](https://www.ruby-forum.com/t/migrating-a-column-to-foreign-key-and-foreign-table/172849/1 "2009-07-28T23:43:17Z")

</div>

I’m attempting to write a migration that adds a foreign key column to  
a table to replace an existing attribute (a string to an object). I’d  
like to add the column, convert the existing data into a new entry on  
the foreign table and then set the foreign key value.

I’m successfully able to add the column, but it doesn’t seem like the  
objects mapped to the foreign table get created, as after the  
migration completes the foreign table is empty and the foreign key  
values are very large, seemingly random integers.

I’ve tried doing Object.new + Object.save and Object.create, but  
neither seems to work. I’ve also tried both of those inside of a  
transaction within the ‘self.up’ that’s marked as ‘:requires\_new’.

Any thoughts on what I might be doing wrong or how to debug this? Any  
links to examples?

Thanks.

---

<div class="post-metadata">

**Author:** ![ate50eggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/fbc32d/32.png) [@ate50eggs](https://www.ruby-forum.com/u/ate50eggs)\
**Post date:** [July 29, 2009, 1:02am UTC](https://www.ruby-forum.com/t/migrating-a-column-to-foreign-key-and-foreign-table/172849/2 "2009-07-29T01:02:11Z")

</div>

Nathan B. wrote:

> I’m attempting to write a migration that adds a foreign key column to  
> a table to replace an existing attribute (a string to an object). I’d  
> like to add the column, convert the existing data into a new entry on  
> the foreign table and then set the foreign key value.
> 
> I’m successfully able to add the column, but it doesn’t seem like the  
> objects mapped to the foreign table get created, as after the  
> migration completes the foreign table is empty and the foreign key  
> values are very large, seemingly random integers.
> 
> I’ve tried doing Object.new + Object.save and Object.create, but  
> neither seems to work. I’ve also tried both of those inside of a  
> transaction within the ‘self.up’ that’s marked as ‘:requires\_new’.
> 
> Any thoughts on what I might be doing wrong or how to debug this? Any  
> links to examples?
> 
> Thanks.

From your project type:

script/generate migration AddForeignKeyColumnToTableName  
… or if windows …  
ruby script/generate migration AddForeignKeyColumnToTableName

The name of the migration can be anything. I like to use Capitalized  
letters for each new word to describe what I’m doing the migration for.  
In the case above, a migration file will be named  
add\_foreign\_key\_column\_to\_table\_name in your migrations folder.

Go to your database migrations “migrations” folder and open the new  
migration we just created so you can edit it.

def self.up  
add\_column :table, :key\_id, :integer

@model = Model.find(:all)  
@model.each do |model|  
model.update\_attribute(:key\_id, model.column\_name\_to\_convert)  
end

end

def self.down  
remove\_column :table, :key\_id  
end

This should get you close to what you are after. What we are going to  
replace in this code are the following:

key\_id = the name of the foreign\_id you want to name your column  
table = the name of your table (pluralized)  
model = the name of the model you are querying to convert the key for.  
column\_name\_to\_convert = the name of the column you are using to convert  
data from to the new key\_id column.

After you do this, it will perform the following, in order:

Add your foreign\_key column to your model’s table.  
Find all records in your model.  
Iterate through all records in your model.  
Change the data for key\_id with the data for column\_name\_to\_convert’s  
data.

Make sure the types match. If you are converting from a string you need  
to use .to\_i, etc.

Hopefully, this works. It should at first glance.

---

<div class="post-metadata">

**Author:** ![ate50eggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/fbc32d/32.png) [@ate50eggs](https://www.ruby-forum.com/u/ate50eggs)\
**Post date:** [July 29, 2009, 1:04am UTC](https://www.ruby-forum.com/t/migrating-a-column-to-foreign-key-and-foreign-table/172849/3 "2009-07-29T01:04:37Z")

</div>

Also, one other note. I wouldn’t remove the column you are migrating  
data from until you confirm that the data was migrated. Therefore, you  
would remove the old column after the first one succeeded by performing  
a separate migration.

I hope that helps.