Re: innodb foreign key

[R.Dobson]

whoops, yes, thanks, missed that

Victor Pendleton wrote:

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]