2

I followed this guide on how to change the Primary Key:

http://www.lshift.net/blog/2013/09/30/changing-the-primary-key-type-in-ruby-on-rails-models/comment-page-1

Here is my code:

class Pk2 < ActiveRecord::Migration
  def up
    remove_column :contracts, :id # remove existing primary key
    rename_column :contracts, :contractId, :id # rename existing UDID column
    execute "ALTER TABLE contracts ADD PRIMARY KEY (id);"
  end

  def down
    # Remove the UDID primary key. Note this would differ based on your database
    execute "ALTER TABLE contracts DROP CONSTRAINT table_pkey;"
    rename_column :contracts, :id, :contractId
    add_column :contracts, :id, :primary_key
  end
end

The error I keep getting is "Syntax around ALTER TABLE table ADD PRIMARY KEY"

Please help. Thank you.

SSR
  • 6,398
  • 4
  • 34
  • 50
Trung Tran
  • 13,141
  • 42
  • 113
  • 200
  • 1
    Post the `full error stack`. – Pavan May 24 '14 at 18:35
  • 1
    http://stackoverflow.com/questions/19050978/problems-setting-a-custom-primary-key-in-a-rails-4-migration – Richard Peck May 24 '14 at 18:36
  • http://stackoverflow.com/questions/1200568/using-rails-how-can-i-set-my-primary-key-to-not-be-an-integer-typed-column – Richard Peck May 24 '14 at 18:37
  • Does the migration successfully `rename` the `:contractId` column to `:id`? – Richard Peck May 24 '14 at 18:38
  • No, it's still contractId @RichPeck – Trung Tran May 24 '14 at 18:39
  • 1
    Why don't you try running the migration successfully, then creating a new migration with the `execute` command? – Richard Peck May 24 '14 at 18:53
  • that worked...thanks a lot @RichPeck! I broke the code into 3 separate migrations... I do wonder why it didn't work when I ran the whole migration together though. – Trung Tran May 24 '14 at 19:12
  • I don't know! It was just a hunch -- I'll write an answer to clarify the solution for posterity – Richard Peck May 24 '14 at 19:13
  • @RichPeck when I click "Show" I get this error No route matches {:id=>#} missing required keys: [:id] NOTICE it says missing required keys: [:id] - could this have something to do with changing the PK?? – Trung Tran May 24 '14 at 19:24
  • It says ` id: nil` - basically means that attribute is not present in your db. Have you checked if the `id` (the value) exsists in the db? If so, it could be a PK issue; else it will likely be that you've not populated the `id` column for that record – Richard Peck May 24 '14 at 19:33
  • @RichPeck I checked my db schema and there is an :id field. Correction - I'm not getting this error when I click on the "show" page. I am getting this error when I am on the index page... Please help lol – Trung Tran May 24 '14 at 19:50
  • Hmmmmm - are you sure you've got a value in the column? – Richard Peck May 24 '14 at 19:51
  • Yes @RichPeck: create_table "contracts", id: false, force: true do |t| t.string "name" t.date "awardDate" t.date "expirationDate" t.decimal "awardAmount", precision: 10, scale: 3 t.decimal "obligatedAmount", precision: 10, scale: 3 t.decimal "invoicedAmount", precision: 10, scale: 3 t.datetime "created_at" t.datetime "updated_at" t.string "id" end – Trung Tran May 24 '14 at 19:53
  • Nooo I meant have you have actually got a "value" in the `id` column? Like do you have some data available? – Richard Peck May 24 '14 at 19:56
  • @RichPeck The Contracts that I made before I changed the primary key do not have an :id value. However, Contracts that I made after changing the primary key do have a :id field. I just went into my console and gave an :id value to the earlier ones and I can load the index page now. THANK YOU SO MUCH FOR YOUR HELP!! – Trung Tran May 24 '14 at 20:50
  • :) no problem! Keep the questions coming! – Richard Peck May 24 '14 at 20:50

1 Answers1

4

The answer seemed to be that the OP separated the migration into 3 files:

1. Remove `:id` column
2. Rename `:contractId` column to `:id`
3. run `execute "ALTER TABLE contracts ADD PRIMARY KEY (id);"`

This allowed the OP to successfully run the migration

Some other resources:

Community
  • 1
  • 1
Richard Peck
  • 76,116
  • 9
  • 93
  • 147