Create missing auto increment attribute with rails migration

Question

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?

score 7 · Accepted Answer · answered Feb 02 '10 at 13:00

7

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.

answered Feb 02 '10 at 13:00

Simone Carletti

173,507
49
363
364

thanks alot @Simone Carletti, wasted whole day to solve the problem, but now it's solved and thanks DGM too, for posting question specific to my case. Can you both give me idea on writing stored procedures in rails 4.1 in a fancy way, hoping you guys will help me again. Happy coding. – roarfromror Apr 10 '15 at 11:49
I don't know of much activity in rails using stored procedures - some corners of rails have little use for the more powerful sides of SQL, others just don't know about it... anyway, at the very least you could use this same form to write the SQL to create the stored procedure. – DGM Apr 10 '15 at 19:33

score 6 · Answer 2 · answered Apr 26 '10 at 12:49

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...

score 5 · Answer 3 · edited May 23 '17 at 12:22

5

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;

Create missing auto increment attribute with rails migration

3 Answers3

Linked

Related