CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresql
Bryan Spence
Bryan Spence

Reputation: 133

How to change already added string column to a Reference in Rails

I've already created a model in Rails to collect some user information

I created the columns as :string initially but I've since changed the way this data is looked up and entered by using separate populated models.

Now instead of entering into these fields as string - i want these columns to be "references" instead.

Is there an easy way to change from the string to reference without having to create a new model entirely?

*do not need to save the existing data

Upvotes: 3

Views: 8330

Answers (5)

streetlogics
streetlogics

Reputation: 4730

Wanted to add a simpler alternative to the accepted answer that preserves data:

class ChangeStringToInt < ActiveRecord::Migration[5.1]
  def up
    change_column :table_name, :field_name, :integer, null: false, references: :table_referenced, using: 'field_name::integer'
    add_index :chapter_actions, :field_name
  end

  def down
    change_column :table_name, :field_name, :string, null: false, using: 'field_name::character varying'
    remove_index :table_name, :field_name
  end
end

Upvotes: 1

rwxdash
rwxdash

Reputation: 76

Here is another solution, without dropping the column itself (not exactly in my case). I'm not sure though if this is the best solution.

In my case, I have a tickets table that holds purchase_uid in itself. I decided to keep purchases in another table after making the necessary improvements in our backend. Purchases table has uuid as the primary key. Given this background, here is my migration to change my column into a reference.

class AddPurchaseRelationToTickets < ActiveRecord::Migration[5.2]
  def up
    change_column :tickets, :purchase_uid, :uuid, references: :purchase, foreign_key: true, using: 'purchase_uid::uuid'
  end

  def down
    change_column :tickets, :purchase_uid, :string
  end
end

In my case, since string doesn't automatically cast into uuid, purchase_uid were dropped and recreated as well. However, if you decide to keep the column type same, I don't think it will be a problem.

Upvotes: 1

Peter H&#248;jlund Palluth
Peter H&#248;jlund Palluth

Reputation: 901

Is there any data in the strings you would like to save?

Or is it just because it has the same name?

You don't have to create a new model.

You could create a simple migration 

remove_column :table, :your_column_name, :string
add_column :table, :your_column_name, :integer, references: :your_parent_model

Upvotes: 1

Donald Chiang
Donald Chiang

Reputation: 167

You can add a temporary string column to save the string column first:

rails g migration add_temporary_string_column_to_model temporary_string_column:string

And run rails console:

SomeModel.all.each do |some_model|
    some_mode.temporary_string_column = some_mode.string_column
    some_mode.save
end

And now you can change your original string column's type to references which is an int(4) column in MySQL, migration like this:

class ChangeFormatInSomeTable < ActiveRecord::Migration
    def change
        change_column :some_table, :string_column, :references
    end
end

Finally, you can run rails console again to convert the string data to integer like this:

SomeModel.all.each do |some_model|
    some_mode.string_column = some_mode.temporary_string_column.to_i
    some_mode.save
end

And at last, remove the temporary string column:

rails g migration remove_temporary_string_column_from_model temporary_string_column

Upvotes: 1

Rahul Kumar Das
Rahul Kumar Das

Reputation: 99

You can create migrations to serve the exact purpose.

rails generate migration AddAddressToUsers address:references

This will create a migration file in db/migrate directory. Then run: rails db:migrate to run migration and make changes in your database.

Don't forget to create associations in your models (belongs_to, has_many, etc.) depending on your system structure.

Upvotes: 0

Related Questions