innodb foreign key

[R.Dobson]

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]