Change primary_key column name

rubyonrails-talk
1

Hi,

after changing a primary key column name, the auto-increment information (MySQL) and sequence (Oracle) are lost. What is the correct way to rename primary keys?

Thanks,

Gustavo

2

Hi Gustavo,

You will have to use migrations to make changes to the database...did you migrate the changes or did you change the primary key field directly from the database?

3

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post #968329:

Hi,

after changing a primary key column name, the auto-increment information (MySQL) and sequence (Oracle) are lost.

So what? The actual value of the key should never be significant anyway.

What is the correct way to rename primary keys?

rename_column :table, :key, :id

Thanks, Gustavo

Best,

4

Sorry Marnen, I think I didn't make myself clear, let me show one example which ilustrates betteer what I'm trying to say. Consider this user table, in MySQL database:

5

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post #968351:

"Gustavo de S Carvalho Honorato" <gustavohonorato wrote in post #968329: > Hi, > > after changing a primary key column name, the auto-increment information > (MySQL) and sequence (Oracle) are lost.

So what? The actual value of the key should never be significant anyway.

Sorry Marnen, I think I didn't make myself clear, let me show one example which ilustrates betteer what I'm trying to say. Consider this user table, in MySQL database:

+----------+--------------+------+-----+---------+----------------+ > Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ > id | int(11) | NO | PRI | NULL | auto_increment | > username | varchar(255) | YES | | NULL | | > password | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+

Look that id column has auto_increment extra.

When I rename :id column using "rename_column :users, :id, :key" my new :key column loses its "auto_increment" as bellow:

+----------+--------------+------+-----+---------+-------+ > Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ > key | int(11) | NO | PRI | 0 | | > username | varchar(255) | YES | | NULL | | > password | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+

I did not get this to work with the default rename_column function (I believe there is a bug, more details below).

So at first, I resorted to this (based on the standard mysql documentation for ALTER TABLE/CREATE TABLE):

class RenamePrimaryKeyPayments < ActiveRecord::Migration   def self.up     connection.execute("ALTER TABLE payments CHANGE id `key` INTEGER NOT NULL AUTO_INCREMENT;")   end

  def self.down     connection.execute("ALTER TABLE payments CHANGE `key` id INTEGER NOT NULL AUTO_INCREMENT;")   end end

Note: be careful with direct SQL here, you cannot access the `key` column without the backticks, maybe a reserved word in SQL ...