Click to See Complete Forum and Search --> : Help with foreign key error 1005


landysaccount
10-31-2009, 10:50 AM
Hello.

I'm trying to test something I read in a book but, I'm getting an error and can't figure out where is the mistake.

I get error 105/1005 cant' create table survey_responses.

create table survey_question(
quest_id int(3) unsigned not null auto_increment,
question varchar(80) not null,
primary key( quest_id )
)
engine=innodb;

/* Survey answers */
create table survey_anwers(
ans_id int(3) unsigned not null auto_increment,
quest_id int(3) unsigned not null,
ans_text varchar(40) not null,
primary key( ans_id ),
foreign key( quest_id ) references survey_question( quest_id )
)
engine=innodb;
/* Survey responses */
create table survey_responses(
resp_id int(10) unsigned not null auto_increment,
quest_id int(3) unsigned not null,
ans_id int(3) unsigned not null,
primary key ( resp_id ),
foreign key( quest_id ) references survey_question( quest_id ),
foreign key( ans_id ) references survey_answers( ans_id )
)
engine=innodb;

Both foreign keys are of the same type and size... What am I doing wrong?

Thanks in advanced for your help.

landysaccount
10-31-2009, 11:05 AM
Solved the problem.

Found out more information on the error by executing: SHOW ENGINE INNODB STATUS

That guided me to notice that the table name survey_answer was misspelled so, there wasn't a match.

How stupid that was.