Description:
Some column name changes not possible to do in one step
You have two fields named 'a' and 'b'
If you want to rename 'a' to 'b'
and 'b' to a' in one step you just can't because the generated SQL
renames only one field leading to an error.
The workaround is to do it in three steps just like a swap.
'a' to 'b1'
'b' to 'a'
'b1' to 'b'
NOTE: this gets even more complicated if one or more fields are part of a key.
MySQL Administrator 1.2.10
MySQL Server Information
--------------------------------------------------------------------------------
Connected to MySQL Server Instance
Username: root
Hostname: localhost
Port: 3306
--------------------------------------------------------------------------------
Server Information
MySQL Version: MySQL 5.0.27-community-nt via TCP/IP
Network Name: localhost
IP: 127.0.0.1
--------------------------------------------------------------------------------
Client Information
Version: MySQL Client Version 5.1.11
Network Name: xxxxxxxxxxx
IP: xxxxxxxxxxx
Operating System: Windows XP
Hardware: 2x Intel(R) Core(TM)2 CPU T7200 @ 2.00GHz, 2.0 GB RAM
How to repeat:
Prerequisite:
Have schema with a table defining three fields
Open MySQL Administrator 1.2.10
From catalogs select a schema you can fool around with.
See screen shot s1
In the right pane activate the 'Schema Tables' tab and open a
table like t2 and say 'edit'
In the table at the top of the table Editor:
Now in one step change field 'b' to 'b1' and
field 'c' to 'c1'
Say apply changes and you will get (screen shot s2)
ALTER TABLE `test`.`t2`
CHANGE COLUMN `b` `b1` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
CHANGE COLUMN `c` `c1` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
In the 'Confirm Table Edit' dialog press 'Cancel' then say 'Discard Changes'
Now try to rename 'b' to 'c' and 'c' to 'b' you will get (screen shot s3)
ALTER TABLE `test`.`t2`
CHANGE COLUMN `b` `c` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
The expectation here would be to see :
ALTER TABLE `test`.`t2`
CHANGE COLUMN `b` `c` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
CHANGE COLUMN `c` `b` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
