create table tt1 ( r1 int(10), key (r1), constraint r1 foreign key (r1) references pfwuser(usrid) ) type=InnoDB;
If I try to change the column type like this: alter table tt1 modify r1 varchar(255);
I get an error:
mysql> alter table tt1 modify r1 varchar(255); ERROR 1025 (HY000): Error on rename of './pfw282beta/#sql-27f6_1326' to './pfw282beta/tt1' (errno: 150)
However, if I first drop the foreign key, the alter table works: mysql> show create table tt1;
| tt1 | CREATE TABLE `tt1` ( `r1` int(10) default NULL, KEY `r1` (`r1`), CONSTRAINT `0_1227` FOREIGN KEY (`r1`) REFERENCES `pfwuser` (`usrid`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 |
mysql> alter table tt1 drop foreign key 0_1227; Query OK, 0 rows affected (0.03 sec)
mysql> alter table tt1 modify r1 varchar(255); Query OK, 0 rows affected (0.03 sec)
In almost all other databases, when changing a column type, all constraints related to the column are automatically dropped first before the new column definition is applied. Since our application automatically creates and alters tables according to end-user commands (via the interface), we need the constraints to be dropped automatically. As far as I know, there is no automatic way to know the constraint names related to a column without parsing the result of "show create table". If there were, I could just drop the foreign key before altering the table. As far as I know there is no way to name the constraint when its created.
By the way, this same problem applies when I try to drop the column - first I have to drop the foreign key, then I can drop the column.
So to summarize, our problems are the following:
1. cannot change column type without first dropping the foreign key (if there is one)
2. cannot drop the column without first dropping the foreign key (if there is one)
3. cannot explicitly define a constraint's name when creating it.
4. cannot automatically figure out the names of the constraints associated with a column without manually parsing the results of "show create table"
Sorry for the long winded exlanation. Any help is appreciated. All this may be by design, but I would like to verify that.
Thanks, /gilad
-- ------------------------------------------------------------------------
*Gilad Ezra Buzi *R&D Engineer Open Source Advocate
*Concatel* Avenida Puertos de Europa 100, 08040 Barcelona (Spain) tel. +34.93.567.97.10 fax +34.93.567.97.11
http://www.concatel.com
