MySQL Forums
Forum List  »  InnoDB

Error when adding a foreign key constraint
Posted by: Steffan Sørenes
Date: March 23, 2009 05:37AM

I have this table:

CREATE TABLE gene (
gid SMALLINT NOT NULL AUTO_INCREMENT,
gene_if VARCHAR(9) NOT NULL,
chr TINYINT NOT NULL,
models TINYINT NOT NULL,
solub VARCHAR(30),
pts VARCHAR(30),
variant_align VARCHAR(30),
update_tair VARCHAR(6) NOT NULL DEFAULT 'TAIR8',
date_update_araperox TIMESTAMP,
PRIMARY KEY (gid),
UNIQUE (gene_if)
);

an this:

CREATE TABLE model (
gid SMALLINT NOT NULL,
mid TINYINT NOT NULL,
primacronym VARCHAR(20),
primfullname VARCHAR(50),
description TEXT,
model_type VARCHAR(20),
PRIMARY KEY (gid, mid),
FOREIGN KEY (gid) REFERENCES gene(gid)
);

It works fine to create these tables. But when I try to create the table below i receive this error: ERROR 1005 (HY000): Can't create table './db_steffans/modeldata.frm' (errno: 150)

CREATE TABLE modeldata (
gid SMALLINT NOT NULL,
mid TINYINT NOT NULL,
size_bp SMALLINT,
size_aa SMALLINT,
model VARCHAR(20),
brief_description TINYTEXT,
extended_description TEXT,
swiss_protid VARCHAR(8),
mw VARCHAR(8),
pi VARCHAR(8),
protein_seqs VARCHAR(40),
nucl_seqs VARCHAR(40),
PRIMARY KEY (gid, mid),
FOREIGN KEY (gid) REFERENCES gene(gid),
FOREIGN KEY (mid) REFERENCES model(mid)
);

I tried to create the table without Foreign keys and it worked. So i tried to add FOREIGN KEY (gid) REFERENCES gene(gid) manually with alter table and it worked, but when i tried to add FOREIGN KEY (mid) REFERENCES model(mid) the same error appear: ERROR 1005 (HY000): Can't create table './db_steffans/#sql-db5_e57e8.frm' (errno: 150)

What is wrong? All tables use INNODB.

Options: ReplyQuote


Subject
Views
Written By
Posted
Error when adding a foreign key constraint
4759
March 23, 2009 05:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.