CODEWITHSUNDEEP
ruby-on-railsactiverecordauto-incrementrails-migrations
Somesh
Somesh

Reputation:

Add auto increment back to primary key column in Rails

By mistake I removed the autoincrement option from id field of my table. Can anyone tell me how I can reinsert the option of autoincrement back through migration?

Upvotes: 19

Views: 24875

Answers (6)

Ankur Kothari
Ankur Kothari

Reputation: 908

This worked for me, created a primary column and gave ids to all the rows.

add_column :tags_posts_rel, :id, :primary_key

Upvotes: 1

Arvind
Arvind

Reputation: 2781

Change Column

Rails5

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Rails6

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Add Column Rails5

add_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Rails6

add_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Upvotes: 0

Garrett Simpson
Garrett Simpson

Reputation: 1619

For those of you who came here (like I did) in an effort to figure out how to make a custom id column that uses bigint instead of int, what I didn't realize is that for Rails 5.1 and above, bigint is the default type for id

https://github.com/rails/rails/pull/26266

Upvotes: 0

drewish
drewish

Reputation: 9380

I didn't check other versions but on Rails 5 you can just set the auto_increment option:

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true

Or if you want a bigint:

change_column :table_name, :id, :bigint, null: false, unique: true, auto_increment: true

Upvotes: 9

vladr
vladr

Reputation: 66721

Try:

change_column :my_table, :id, :primary_key

or

my_table.change_column :id, :primary_key

Certain Rails database adapters may not let you call change_column on the primary key. If that is the case then you can always call execute to perform the change using SQL directly:

MySQL:

execute('ALTER TABLE "my_table" CHANGE "id" "id"
  bigint DEFAULT NULL auto_increment PRIMARY KEY')

PostgreSQL (method 1):

max_id = execute(%%Q{SELECT id FROM "my_table" ORDER BY "id" DESC
  LIMIT 1}).to_a.first
execute(%%Q{CREATE SEQUENCE "my_table_id_seq" START #{max_id+1}})
execute(%%Q{ALTER TABLE "my_table" ALTER COLUMN "id"
  TYPE bigint})
execute(%%Q{ALTER TABLE "my_table" ALTER COLUMN "id"
  SET DEFAULT nextval('my_table_id_seq'::regclass)})
execute(%%Q{ALTER TABLE "my_table" ADD PRIMARY KEY("id")})

PostgreSQL (method 2):

max_id = execute(%%Q{SELECT "id" FROM "my_table" ORDER BY "id" DESC
  LIMIT 1}).to_a.first
execute(%%Q{ALTER TABLE "my_table" RENAME COLUMN "id" TO "id_orig"})
execute(%%Q{ALTER TABLE "my_table" ADD COLUMN "id" bigserial NOT NULL})
execute(%%Q{UPDATE "my_table" SET "id"="id_orig"})
execute(%%Q{ALTER SEQUENCE "my_table_id_seq" RESTART #{max_id+1}})
execute(%%Q{ALTER TABLE "my_table" DROP COLUMN "id_orig"})

If you do not want to use bigint/bigserial (64-bit), use int(11)/integer/serial instead.

Upvotes: 21

Leonid Shevtsov
Leonid Shevtsov

Reputation: 14189

Your Postgres code does not work, it's impossible to use serial or bigserial in an ALTER TABLE statement. Correct SQL for PostgreSQL is 

ALTER TABLE table ALTER COLUMN id TYPE int 
ALTER TABLE table ALTER COLUMN id TYPE bigint

Upvotes: 1

Related Questions