One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
-----Original Message----- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below 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, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
