MySQL trouble with foreign keys

[JW310]

I'm working on a database project for one of my classes, and I've got a bunch of tables that I'm trying to create in MySQL. In most cases, I'm able to create tables that have foreign keys referencing a field in another table. However, I'm running into a couple problems. The attached code has the SQL commands I'm using to try to create the tables. The Locations table creates just fine, but when I try to create the Classes table, I get the following error:

"#1005 - Can't create table './database name/Classes.frm' (errno: 150)"

However, if I get rid of the foreign key reference, I can create the table just fine.

I've done some searching, and it appears that particular error will happen when trying to create foreign keys without an index, but I'm assuming that since `Location_ID` is the primary key on the `Locations` table, I don't need to create another index on that table, right?

What other possibilities might I try to get this to work? The server is running MySQL 4.0.17, and upgrading to a newer version is not possible (hosting is being provided by my school, and they won't upgrade to a newer version of MySQL).

Thanks,
JW

 

[dighn]

you need to index Location_ID in Classes

eg INDEX(Location_ID)

 

[JW310]

Should I do that before or after the foreign key statement?

I'll give it a try, and see what happens.

Thanks!
JW

 

[dighn]

Originally posted by: JW310
Should I do that before or after the foreign key statement?

don't think it matters

but i do it after

 

[JW310]

Put it before the foreign key statement, worked beautifully. Thanks very much!


JW

 

[dighn]

Originally posted by: JW310
Put it before the foreign key statement, worked beautifully. Thanks very much!


JW

glad ot help. ran into the same problem a couple of months ago 🙂