Hi,
I have a column 'id' within a table :
CREATE TABLE `reference` ( *`*id*`* smallint(5) unsigned NOT NULL auto_increment, `study_name` text, `author` text NOT NULL, `date` date NOT NULL default '0000-00-00', `reference` varchar(250) NOT NULL default '', `title` varchar(250) NOT NULL default '', `pmid` int(15) default NULL, `project` varchar(35) default NULL, `abstract` text, `datasource` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB
that i need to change from smallint to int
There are a number of foreign key constraints from other columns in other tables on this column: eg
CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `num_peds` int(7) unsigned default NULL, `affected` int(7) unsigned default NULL, `unaffected` int(7) unsigned default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_178` FOREIGN KEY (`gene_ID`) REFERENCES `gene` (`id`), CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE ) TYPE=InnoDB
Upon trying to modify the id columns in the reference table, i'm getting the following
mysql> alter table reference modify id int; ERROR 1025: Error on rename of './nugenob/#sql-2ed1_e001' to './nugenob/reference' (errno: 150) mysql>
It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution?
cheers Rich
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
