MySQL Notes

I just posted on one of my project_specific blogs about some of the issues we've encountered trying to move data from an Oracle db to MySQL.

I was banging my head against a wall trying to figure out why this:

create table ecafetest.temp_stats (
statistic_id int not null
, survey_id int
, section_id int
, org_id int
, question_id int
, answer_id int
, answer_order_num int
, response_id int
, srs_id int
, urs_id int
, cnt int not null default '0'
, FOREIGN KEY (statistic_id) REFERENCES statistic(id) ON DELETE CASCADE
, FOREIGN KEY (survey_id) REFERENCES survey(id) ON DELETE CASCADE
, FOREIGN KEY (section_id) REFERENCES section(id) ON DELETE CASCADE
, FOREIGN KEY (org_id) REFERENCES org(id) ON DELETE CASCADE
, FOREIGN KEY (question_id) REFERENCES question(id) ON DELETE CASCADE
, FOREIGN KEY (answer_id) REFERENCES answer(id) ON DELETE CASCADE
, FOREIGN KEY (response_id) REFERENCES response(id) ON DELETE CASCADE
, FOREIGN KEY (srs_id) REFERENCES section_response_set(id) ON DELETE CASCADE
, FOREIGN KEY (urs_id) REFERENCES user_response_set(id) ON DELETE CASCADE
, primary key(statistic_id, question_id, answer_id, response_id)
) Engine=InnoDB;

Was generating a table where question_id, answer_id, and response_id were declared "not null" and my insert was failing because response_id was null. Imagine how silly I felt when I finally noticed the primary key. MySQL doesn't allow null values in a primary key, although they are allowed in all other indexes. So, while I may not have written "response_id int not null," through the primary key I had in effect done so.

I was dealing with this at the end of yesterday, and wasn't making any progress on it. I finally went home, and within a minute of looking at it this morning, saw my problem. It's amazing how often walking away from a problem brings you the solution.