Change primary_key column name

SSGustavo_de_SSS_Ca · December 14, 2010, 5:31pm

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

SSGustavo_de_SSS_Ca · December 14, 2010, 5:39pm

“Gustavo de Sá Carvalho H.” <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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

SSGustavo_de_SSS_Ca · December 14, 2010, 6:27pm

On Tue, Dec 14, 2010 at 2:39 PM, Marnen Laibow-Koser
[email protected] wrote:

“Gustavo de S Carvalho H.” <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 | |
±---------±-------------±-----±----±--------±------+

SSGustavo_de_SSS_Ca · December 14, 2010, 7:44pm

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?

SSGustavo_de_SSS_Ca · December 18, 2010, 2:36am

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#968351:

On Tue, Dec 14, 2010 at 2:39 PM, Marnen Laibow-Koser
[email protected] wrote:

“Gustavo de S Carvalho H.” <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 …

mysql> select key, testing from payments;
±----±--------+
| key | testing |
±----±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | delta |
±----±--------+
4 rows in set (0.00 sec)

mysql> select id, testing from payments;
±—±--------+
| id | testing |
±—±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | delta |
±—±--------+
4 rows in set (0.00 sec)

mysql>

The good news is that the AUOT_INCREMENT automatically does the right
thing and starts inserting new records with the primary key 1 higher
than
the existing records. I was concerned how this works out for records
for which a transaction was started, but was rolled back … or if the
highest record was deleted in the meanwhile).

mysql> DELETE from payments WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select id, testing from payments;
±—±--------+
| id | testing |
±—±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
±—±--------+
3 rows in set (0.00 sec)

mysql> INSERT INTO payments (testing) VALUES (‘zeta’) ;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select id, testing from payments;
±—±--------+
| id | testing |
±—±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 5 | zeta | # GOOD ! it remembers about the 4 that is consumed
±—±--------+
4 rows in set (0.00 sec)

mysql> DELETE from payments WHERE id = 5;
Query OK, 1 row affected (0.00 sec)

migrating …

mysql> select key, testing from payments;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘key, testing from payments’ at line 1
mysql> select key, testing from payments;
±----±--------+
| key | testing |
±----±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 6 | peta | # GOOD !! it still remembers the 4 and 5 consumed
±----±--------+
4 rows in set (0.00 sec)

mysql> DELETE FROM payments WHERE key = 6; # deleting the 6 here
Query OK, 1 row affected (0.00 sec)

Changing the migration to use “rename_column” first and re-add the
AUTO_INCREMENT afterwards:

def self.down
rename_column :payments, :key, :id
connection.execute(“ALTER TABLE payments CHANGE id id INTEGER NOT
NULL AUTO_INCREMENT;”)
end

which does:
…
SQL (0.1ms) SELECT schema_migrations.version FROM
schema_migrations
SQL (0.3ms) SHOW COLUMNS FROM payments LIKE ‘key’
SQL (8.3ms) ALTER TABLE payments CHANGE key id int(11) NOT NULL
SQL (5.2ms) ALTER TABLE payments CHANGE id id INTEGER NOT NULL
AUTO_INCREMENT;
AREL (0.5ms) DELETE FROM schema_migrations WHERE
schema_migrations.version = ‘20101217234428’
…

and then you get:

mysql> select id, testing FROM payments ;
±—±--------+
| id | testing |
±—±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
±—±--------+
3 rows in set (0.00 sec)

mysql> INSERT INTO payments (testing) VALUES (‘peta’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select id, testing FROM payments ;
±—±--------+
| id | testing |
±—±--------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | peta | # BAD, we forgot the consumed id’s 5,6,7
±—±--------+
4 rows in set (0.00 sec)

So, I believe the “direct” migration from the 1 column name to the new
column name, while maintaining the AUTO_INCREMENT is required.

Is it possible to add this as an extra option in the Rails migration ?
I did not immediately find on the api.rubyonrails.org site how you could
give an additional option (as a 4th argument then), to rename_column:
rename_column(table_name, column_name, new_column_name).

Digging deeper … this could be a bug in Rails 3.0.3 …

This code in lib/active_record/connection_adapters around line 499,

current_type = select_one(“SHOW COLUMNS FROM
#{quote_table_name(table_name)} LIKE ‘#{column_name}’”)[“Type”]

rename_column_sql = “ALTER TABLE #{quote_table_name(table_name)} CHANGE
#{quote_column_name(column_name)} #{quote_column_name(new_column_name)}
#{current_type}”

Only takes the “type” into account, but … when inspecting what comes
back from the database, there is also “extra” info:

I instrumented:
select_one(“SHOW COLUMNS FROM #{quote_table_name(table_name)} LIKE
‘#{column_name}’”).inspect
and got:

{“Default”=>nil, “Extra”=>“auto_increment”, “Field”=>“key”,
“Key”=>“PRI”, “Null”=>“NO”, “Type”=>“int(11)”}

And I have the impression the “Extra” field is not used, but is required
by mysql to keep the auto_increment active on that column.

So, with this changed version, the behaviour is actually better, with a
standard “rename_column” and not requiring raw SQL in the migration.

peterv@ASUS:~/g/activerecord-3.0.3/lib/active_record/connection_adapters$
diff -u mysql_adapter.rb.ORIG mysql_adapter.rb
— mysql_adapter.rb.ORIG 2010-12-18 02:23:26.000000000 +0100
+++ mysql_adapter.rb 2010-12-18 02:22:47.000000000 +0100
@@ -496,7 +496,9 @@
else
raise ActiveRecordError, “No such column:
#{table_name}.#{column_name}”
end

   current_type = select_one("SHOW COLUMNS FROM

#{quote_table_name(table_name)} LIKE ‘#{column_name}’")[“Type”]

   column_info = select_one("SHOW COLUMNS FROM

#{quote_table_name(table_name)} LIKE ‘#{column_name}’")

```
   current_type = column_info["Type"]
```

   current_type << " AUTO_INCREMENT" if column_info["Extra"] =~

/auto_increment/i
rename_column_sql = “ALTER TABLE
#{quote_table_name(table_name)} CHANGE #{quote_column_name(column_name)}
#{quote_column_name(new_column_name)} #{current_type}”
add_column_options!(rename_column_sql, options)
execute(rename_column_sql)

The migration code (also testing for a column that is not
AUTO_INCREMENT):

class RenamePrimaryKeyPayments < ActiveRecord::Migration
def self.up
rename_column :payments, :id, :key
rename_column :payments, :testing, :greek
end

def self.down
rename_column :payments, :key, :id
rename_column :payments, :greek, :testing
end
end

Up migration log:

SQL (0.1ms) SELECT schema_migrations.version FROM
schema_migrations
SQL (0.4ms) SHOW COLUMNS FROM payments LIKE ‘id’
SQL (2.9ms) ALTER TABLE payments CHANGE id key int(11)
AUTO_INCREMENT NOT NULL
SQL (0.5ms) SHOW COLUMNS FROM payments LIKE ‘testing’
SQL (3.4ms) ALTER TABLE payments CHANGE testing greek
varchar(255) DEFAULT NULL
SQL (1.2ms) INSERT INTO schema_migrations (version) VALUES
(‘20101217234428’)

Down migration:

SQL (0.1ms) SELECT schema_migrations.version FROM
schema_migrations
SQL (0.3ms) SHOW COLUMNS FROM payments LIKE ‘key’
SQL (3.1ms) ALTER TABLE payments CHANGE key id int(11)
AUTO_INCREMENT NOT NULL
SQL (0.4ms) SHOW COLUMNS FROM payments LIKE ‘greek’
SQL (2.6ms) ALTER TABLE payments CHANGE greek testing
varchar(255) DEFAULT NULL
AREL (1.5ms) DELETE FROM schema_migrations WHERE
schema_migrations.version = ‘20101217234428’

If this is relevant, I could try to file bug ticket against Rails 3.0.3.
(and try to add tests).

HTH,

Peter