Rails 4 MySQL bigInt primary key issues and errors

Question

I need to use a 14-digit bigInt as a primary key in a rails 4.1.8 application. Using older posts on SO as a guide, I came up with the following to address this...

    class CreateAcctTransactions < ActiveRecord::Migration
      def change
        create_table "acct_transactions", :id => false do |t|
            t.integer :id, :limit => 8,null: false
            t.integer  "account_id",limit: 8,null: false
            t.integer  "transaction_type_id", null: false
            t.datetime "date",null: false
            t.text     "description",limit: 255
            t.decimal  "amount",precision: 10, scale: 2, null: false
        end
      end
    end

However, this method doesn't really assign "id" as a primary key, it is just another ordinary field. Also, when I get the following error ...

Mysql2::Error: Field 'id' doesn't have a default value: INSERT INTO acct_transactions (account_id, amount, date, description, transaction_type_id) VALUES (224149525446, 222.450361056561, '1970-12-18 00:00:00', 'Transfer', 6)

when I try to run the following seed file...

    account_transactions = []

    accounts.each do |i|
        80.times do |j|
            type = types.sample
        case (type)
            ...
        end

        t = AcctTransaction.new
        t.id = SecureRandom.random_number(99999999999999) # 14-digit BigInt
        t.account_id = accounts[j].id
        t.transaction_type_id = type
        t.date = Time.at((Time.now.month - 18) + rand * (Time.now.to_f)).to_date
        t.description = description
        t.amount = amount

        t.save
        account_transactions << t
      end
    end

The migration runs fine, but the table won't seed and id is not primary. Have I made an error? Or is there a better way to do this?

Much thanks

possible duplicate of [rails3 bigint primary key](http://stackoverflow.com/questions/5880207/rails3-bigint-primary-key) — eritiro, Mar 08 '15 at 19:24

score 1 · Accepted Answer · answered Mar 08 '15 at 19:20

I fixed it by writing the migration with SQL execution like so:

    class CreateAcctTransactions < ActiveRecord::Migration
      def self.up
        # create ACCT_TRANSACTIONS table
          create_table "acct_transactions", id: false, force: true do |t|
            t.integer  "id",                  limit: 8,                            null: false
            t.timestamp "date",                                                     null: false
            t.text     "description",         limit: 255
            t.decimal  "amount",                          precision: 10, scale: 2, null: false
            t.integer  "account_id",          limit: 8,                            null: false
            t.integer  "transaction_type_id",                                      null: false
          end
          execute "ALTER TABLE acct_transactions ADD PRIMARY KEY (id);"
          add_index "acct_transactions", ["account_id"], name: "fk_acct_transactions_accounts1_idx", using: :btree
          add_index "acct_transactions", ["date", "id"], name: "BY_DATE", using: :btree
          add_index "acct_transactions", ["transaction_type_id"], name: "fk_acct_transactions_transaction_types1_idx", using: :btree
      end

      def self.down
        drop_table :acct_transactions
      end
    end

Note the execute statement @ line 12. While I was in there, I also changed the "date" field to a timestamp, which I meant to do originally anyway. It's not pretty and violates "convention" but it works perfectly, so I can move on. Thanks for looking.

This will only work if you run the migrations, not if the database is created using schema.rb (e.g. if you run rake db:setup). — PDug, Feb 26 '16 at 16:15

Rails 4 MySQL bigInt primary key issues and errors

1 Answers1

Linked