2

When I created the table users, the resulting table had a column called id, defined as an integer.

I tried to modify it to bigint, unsigned as follows:

change_column :users, :id, :integer, :limit => 8, :unsigned => true

Which did change it to bigint, but it was no longer an auto-increment column (though it was still identified as the primary index, and it was not set to unsigned (even though rails told me that the migration executed fine)

I then tried doing:

change_column :users, :id, :integer, :limit => 8, :unsigned => true, :null => false, :auto_increment => true

Rails said that the migration executed fine, but nothing changed.

I could try something like:

change_column :users, :id, :primary_index

but that would put me right where I started

I could also try an "execute" statement with MySQL code, but I want to keep the migration file "clean". Has anyone run into this issue?

As an aside, I was also trying to set the default to NULL on another column, researched it here (and Google), with no success.

EDIT:

It seems as if there is no way to edit the column "id" after it is created as part of table creation through a generic migration. The only way to do this is through an "execute" statement with MySQL syntax.

EastsideDev
  • 6,257
  • 9
  • 59
  • 116

2 Answers2

1

Try creating and running this migration:

class ChangeColumnUserIdToAutoIncrement < ActiveRecord::Migration

  def self.up
    execute "ALTER TABLE users modify COLUMN id int(8) AUTO_INCREMENT"
  end

  def self.down
    execute "ALTER TABLE users modify COLUMN id int(8)"
  end
end
fuzzyalej
  • 5,903
  • 2
  • 31
  • 49
Fares
  • 241
  • 1
  • 7
0

Rails is taking care for you the ID field of all the tables you're creating and it's hidden from your migrations and even from the schema.rb file.

So I would advice you to take a step back and think: Why would you want to mess with it? Are you sure that's really needed?

EDIT: This answer seems to be exactly what you're looking for.

Regarding "setting default to NULL", again, why would you do that? All the columns are defaulting to NULL anyway. However, if that's not the case you could do that in a migration:

change_table :users do |t|
  t.string :description, :default => nil
end

Hope that answers your question.

Community
  • 1
  • 1
Agis
  • 32,639
  • 3
  • 73
  • 81
  • The problem with the default ID field setting, is that it's an integer value, set to signed, which means that I am limited to values up to 2,147,483,647. This may seem like a very large number, but I am dealing with social networks data, with tens of millions of records per day (they are eventually archived, but I need to maintain the ID number). So, I need to make it a bigint, unsigned, which gives me up to 18,446,744,073,709,551,615 (Twitter, for instance provides its tweet IDs as bigint, unsigned). – EastsideDev Jun 01 '12 at 11:38
  • I understand that by default, the default value is NULL, but in this case, I accidentally changed it, and was wondering, short of deleting the column and re-creating it, if there is a way to change the default back to null. I've already tried :default => false, but it produces a 0, not a NULL. – EastsideDev Jun 01 '12 at 11:38
  • About the default option I had make an accidental mistake. It's corrected now, can you try that? – Agis Jun 01 '12 at 11:54
  • I've also added a link for your ID-related question. Refer to my original answer. – Agis Jun 01 '12 at 11:59
  • Ah, I have not tried nil yet. I will try it and I'll check the link you provided as well. Thanks. – EastsideDev Jun 01 '12 at 12:08
  • OK, I've seen the Q/A session in the link you provided, and it has a couple of major problems: 1. It would require me to delete all my tables/database, and start over. 2. Even if I could delete my database, the migration file I created a test table, generated an error: uninitialized constant ActiveRecord::ConnectionAdapters::MysqlAdapter When I researched issue #2, no clear answer was available The :default => nil worked. – EastsideDev Jun 01 '12 at 14:36