Re: ERROR 1005: Can't create table (errno: 150)

[Kann Vearasilp]

I do not know what is happening with my foreign key statements as well. There are 3 tables, and the table 2 is referencing table 1 while table 3 is referencing table 2. It is ok to make table 3 referencing table 1, but somehow having table 3 referencing table 2 is not working. Any help??


## this is table 1
DROP TABLE IF EXISTS miRBase;
CREATE TABLE IF NOT EXISTS miRBase (`id` int(11) NOT NULL auto_increment,`accession` varchar(45) default NULL,`name` varchar(45) default NULL,`specie` varchar(45) default NULL,`description` text,`refDBs` text,`comment` text,`seq` text,PRIMARY KEY (`id`),UNIQUE KEY `accession_UNIQUE` (`accession`))engine = innoDB;


### this is table 2
DROP TABLE IF EXISTS mature_miRBase;
CREATE TABLE IF NOT EXISTS mature_miRBase (`id` int(11) NOT NULL auto_increment,`accession` varchar(45),`name` varchar(45),`strand` varchar(45),`evidence` varchar(45),`start` INT,`end` INT,`parent` varchar(45),`seq` text,PRIMARY KEY (`id`),INDEX (`parent`),FOREIGN KEY (`parent`) REFERENCES miRBase(`accession`))engine = innoDB;


## and table 3
DROP TABLE IF EXISTS miRecords;
CREATE TABLE IF NOT EXISTS miRecords (`id` int(11) NOT NULL auto_increment,`PMID` varchar(45),`target_gene_name` varchar(45),`target_gene_refseq` varchar(45),`target_gene_specie` varchar(45),`target_gene_site_number` varchar(45),`target_gene_site_position` varchar(45),`miRNA_name` varchar(45),`miRNA_regulation` varchar(45),`reporter_link_element` varchar(45),`mat_miRNA_acc_num` varchar(45),`stem_miRNA_acc_num` varchar(45),`original_description` text,PRIMARY KEY (`id`),INDEX (`mat_miRNA_acc_num`),FOREIGN KEY (`mat_miRNA_acc_num`) REFERENCES mature_miRBase(`accession`))engine = innoDB;