Create missing auto increment attribute with rails migration
I'm writing a migration to convert a non-rails app into the right format for rails - one of the tables for some reason does not have auto increment开发者_高级运维 set on the id column. Is there a quick way to turn it on while in a migration, maybe with change_column
or something?
You need to execute an SQL statement.
statement = "ALTER TABLE `users` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT"
ActiveRecord::Base.connection.execute(statement)
Note this is just an example. The final SQL statement syntax depends on the database.
If you're on postgesql, a single request won't make it. You'll need to create a new sequence in the database.
create sequence users_id_seq;
Then add the id column to your table
alter table users
add id INT UNIQUE;
Then set the default value for the id
alter table users
alter column id
set default nextval('users_id_seq');
Then populate the id column. This may be quite long if the table has many rows
update users
set id = nextval('users_id_seq');
Hope this helps postgresql users...
The Postgres answer by @jlfenaux misses out on the serial
type, which does all of it for you automatically:
ALTER TABLE tbl add tbl_id serial;
More details in this related answer.
精彩评论