Date: 08/02/07 (MySQL Communtiy)    Keywords: mysql, database, sql, microsoft, google

This one's got me at the end of my rope, and none of the answers I'm finding searching in the many places I've searched have given me any help, so I'll ask you guys.

First off: I am brand new (as in a couple of weeks) to MySQL, after moving to Ubuntu (Feisty) at home. (Goodbye Microsoft.) I'm a SQL Server DBA/DWA/Database Developer/jack-of-all-trades at work, so I do have some DB experience, just not with MySQL.

Anyway, I've created a schema and the tables in it. Now I'm trying to relate those tables. All use the InnoDB engine.* The guinea pigs I'm using are the BOOK and AUTHOR tables, and I'm trying to relate the ID field in AUTHOR to the AUTHORID field in BOOK. (And, yes, I've tried changing the name of ID to AUTHORID with no success.) Both are type INT. ID/AUTHORID is an ident-seeded primary key. AUTHORID in the BOOK table is a foreign key. Or it's supposed to be, but when I try to relate them, I get the infamous error 1005 (can't create table './directory/filename.frm'). I've found extensive discussion on this at ubuntuforums, forums.mysql.com, and a number of blogs, and have checked everything I've found, modified everything I can, and I still get the error. Basically, if you search on "error 1005" on google and check the first 150 non-repeating pages that come up, I've tried/checked everything that comes up to no avail. I'm at a loss for what do do next, and hoping someone has some ideas.

Also, I tried running a MySQL instance on my XP machine at work, created the exact same tables the exact same way, and tried the relation. It worked on the first try without a hitch.

If anyone's got some help, I'd be truly grateful. Thanks. If more info is needed, I'll report back tonight when I'm actually sitting at my Ubuntu machine.

* As a side note, for philosophical purposes only: why are there so many available engines that don't support foreign keys? Do that many people create non-relational databases? Just wondering.

Source: http://community.livejournal.com/mysql/117098.html