List:General Discussion« Previous MessageNext Message »
From:rich Date:March 16 2005 10:45am
Subject:change a column type and innodb foreign key constraints
View as plain text  
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



Thread
change a column type and innodb foreign key constraintsrich16 Mar
  • Re: change a column type and innodb foreign key constraintsGabriel PREDA16 Mar
    • Re: change a column type and innodb foreign key constraintsSGreen16 Mar