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
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?
"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,
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:
"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 ...
