sqlite & rails: Change primary key column?

Question

I've a table in my database (sqlite) called books which was automatically given an id column by rails 3 when the table was created. The table also has a column called "isbn" which is an integer obviously.

Now I'd like to change the tables primary key to be the isbn column and then drop the original id column.

Can this be done with rails migrations? How?

Answer 1

Most certainly, but dont't do it.

If you want to use isbn to query by instead update your models like this

class Book < ActiveRecord::Base

  def to_param
    self.isbn
  end

end

and your controller

class BooksController

  def show
    @book = Book.find_by_isbn(params[:id])
  end

  # and similar for other actions
end

Answer 2

ISBN isn't an integer -- it can have leading zeros, for example. ISBN should be a text type -- you don't want any sort of "intelligent" numeric adjustments/coercion going on there.

It would be better, I think, to leave things as is and place a unique index on ISBN. Changing SQLite table structure after-the-fact can be awkward. I don't know about the Rails implementation.