Rails 3 migration for adding unsigned int column

Question

I would like to generate a migration to add a column to a table which has a data type of unsigned int. I wish to use it to store IP addresses as mentioned here in this article.

I came across this question but it will make the migration database dependent, any idea how to do it in a better way?

Answer 1

A working solution is shown here that lets you do it slightly more natively inside of a rails migration: unsigned int field in a Ruby on Rails migration?

For longevity's sake the answer is to add a custom specification in your options for a typeless column:

t.column :population, 'integer unsigned'

I believe using 'integer unsigned' is reasonably database independent, but possibly not 100%. You can also use something like 'BIGINT unsigned' if you are willing to lock yourself into a specific database.

Also I'm a bit disappointed in Geoff's answer in that it seems to completely disregard the fact that an unsigned integer while using the same amount of storage space holds a different set of data. If you know you will not be needing negative numbers and are interested in optimizing your data storage needs unsigned ints are valuable. To see the guidelines for mysql, see: http://dev.mysql.com/doc/refman/5.5/en/integer-types.html

It’s important to call out JellicleCat's note below that the schema files will not track this change, so the signed aspect of the column will be lost when the schema is loaded.

Answer 2

step 1:

add activerecord-mysql-unsigned to GemFile

# add unsigned integer support to mysql2 adapter
gem "activerecord-mysql-unsigned", "~> 0.0.1"

step 2: install gems

bundle install

step 3:

use "unsigned: true" in fields you like

t.integer :cost, unsigned: true

refrence : http://rubydoc.info/gems/activerecord-mysql-unsigned/0.0.1/frames

Answer 3

You can do it by execute SQL query,

in case of MySQL query would be

To add new column

ALTER TABLE table_name ADD column_name INT unsigned;

To delete column

ALTER TABLE table_name DROP column_name;

And migration :

class MyMigration < ActiveRecord::Migration
  def self.up
    execute "ALTER TABLE table_name ADD column_name INT unsigned;"
  end

  def self.down
     execute "ALTER TABLE table_name DROP column_name;"
  end
end