rails3 bigint primary key

Question

I would like to create a bigint (or string or whatever that is not int) typed primary key field under Rails 3.

I have a given structure of data, for example:

things
------
id bigint primary_key
name char(32)

The approach I'm currently trying to push:

create_table :things, :id => false do |t| # That prevents the creation of (id int) PK
  t.integer :id, :limit => 8 # That makes the column type bigint
  t.string :name, :limit => 32
  t.primary_key :id # This is perfectly ignored :-(
end

The column type will be correct, but the primary key option will not be present with sqlite3 and I suspect that this is the case for MySQL too.

score 5 · Answer 1 · answered Sep 20 '12 at 14:27

5

I had the same problem. I think the easiest way for a table

accounts 
id bigint primary key 
name char

is

create_table :accounts do |t|
t.string :name
end
change_column :accounts, :id , "bigint NOT NULL AUTO_INCREMENT"

answered Sep 20 '12 at 14:27

skalogirou

147
2
10

2

Almost there. Just do `change_column :accounts, :id, 'bigint'` – Hakan Ensari Oct 25 '12 at 17:22
1

you loose auto increment if you just use 'bigint' – skalogirou Nov 13 '12 at 19:45
4

@skalogirou I just did `change_column :my_primary, :id, 'bigint'` and worked fine, did not lose auto-increment (on Rails 4, Postgres) – Yarin May 24 '16 at 17:07
1

In MySQL/MariaDB, if you just use 'bigint' you will lose auto increment – Jared Beck Sep 04 '18 at 18:59

score 3 · Answer 2 · answered Sep 30 '13 at 17:40

3

For MySQL you can use "SERIAL" which is alias for "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT"

class ChangeUserIdToBigint < ActiveRecord::Migration
  def change
    change_column :users, :id, 'SERIAL'
  end
end

answered Sep 30 '13 at 17:40

Maxim

514
2
7

1

You're right, `SERIAL` works with the notable difference that it adds a `unique` key index to that column as well. – Joshua Pinter Jan 08 '14 at 01:18

score 3 · Accepted Answer · edited May 23 '17 at 12:09

3

Had that myself not long ago and found the answer here: Using Rails, how can I set my primary key to not be an integer-typed column?

You need to set primary_key: false and then use a custom statement before you execute the migration.

EDIT 1: You need to check your database docs for the exact query to perform. It is executed as a regular SQL statement and needs to be database specific. The example in the question I referred to is for Postgre SQL. If you are on MySQL you might have to change that.

edited May 23 '17 at 12:09

Community

1
1

answered May 04 '11 at 08:38

mhenrixon

6,179
4
40
64

`ALTER TABLE things ADD PRIMARY KEY (id);`: Is this something that runs on all databases? – Notinlist May 04 '11 at 11:55
Can I write a case-when structure for that at the migration? Can I test for database variant there? – Notinlist May 04 '11 at 12:21
I'm sure you can but I don't know how. Can you use Rails.env instead? – mhenrixon May 04 '11 at 12:47
In `self.up`: `self.connection.adapter_name`. See `ActiveRecord::ConnectionAdapters::AbstractAdapter`'s documentation. Thanks for the help! – Notinlist May 05 '11 at 09:05

score 1 · Answer 4 · answered Jun 23 '20 at 14:39

1

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

answered Jun 23 '20 at 14:39

Garrett Simpson

1,471
2
17
18

1

NOTE, althought Rails 5.1 makes the default for primary key columns `bigint`, it is `signed`, not `unsigned`. To make the default `unsigned`, check out this Github issue and show your support there: https://github.com/rails/rails/issues/40967 – Joshua Pinter Dec 29 '20 at 21:50

score 0 · Answer 5 · edited May 23 '17 at 12:32

skalogirou's answer is good but the change will not be reflected in schema.rb. So the tasks like db:schema:load and db:test:clone will not create identical DB structure.

The required workaround is to enhance db:schema:load and db:test:clone rake tasks as described here: http://www.lshift.net/blog/2013/09/30/changing-the-primary-key-type-in-ruby-on-rails-models/

This is what I used based on that workaround:

namespace :my_app do
  namespace :db do
    task :after_schema_load_and_db_test_clone => :environment do
    puts 'Changing primary key for :my_table'
    query = 'ALTER TABLE <my_table> CHANGE id id bigint DEFAULT NULL auto_increment'
    ActiveRecord::Base.connection.execute(query)
  end
end


Rake::Task['db:schema:load'].enhance do
  ::Rake::Task['my_app:db:after_schema_load_and_db_test_clone'].invoke
end

Rake::Task['db:test:clone'].enhance do
  ::Rake::Task['my_app:db:after_schema_load_and_db_test_clone'].invoke
end

I found it easier to switch to a SQL dump: `config.active_record.schema_format = :sql` in config/application.rb. — Andy Stewart, Mar 25 '20 at 10:30

score 0 · Answer 6 · answered Jan 17 '18 at 13:34

If you want to convert all tables in Postgres you will need to run this code

class ConvertIntToBigint < ActiveRecord::Migration[5.1]
  def up
    query = <<-SQL
      SELECT tablename AS "tablename"
      FROM pg_tables
      WHERE schemaname = 'public';
    SQL
    connection.execute(query).each do |element|
      if column_exists?(element['tablename'], :id, :integer)
        change_table(element['tablename']) {|t| t.change :id, :bigint }
      end
    end
  end

  def down
  end
end

rails3 bigint primary key

6 Answers6

Linked

Related