Foreign key problems

[KevinM1]

I'm using phpMyAdmin to update my database.  I decided to rebuild some of the tables from scratch.  Unfortunately, the table that's supposed to have foreign keys isn't 'seeing' them.

I have three tables (one of which is currently empty):

[code]
CREATE TABLE positions (
pos_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
goalie_id TINYINT UNSIGNED NOT NULL REFERENCES goalies(goalie_id),
skater_id TINYINT UNSIGNED NOT NULL REFERENCES skaters(skater_id),
position VARCHAR(2) NOT NULL,
jersey_num TINYINT UNSIGNED NOT NULL,
PRIMARY KEY(pos_id));

CREATE TABLE goalies (
goalie_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(40) NOT NULL,
gaa FLOAT UNSIGNED NOT NULL,
save_percent FLOAT UNSIGNED NOT NULL,
shots_faced SMALLINT UNSIGNED NOT NULL,
goals_allowed TINYINT UNSIGNED NOT NULL,
saves SMALLINT UNSIGNED NOT NULL,
shutouts TINYINT UNSIGNED NOT NULL,
games_played TINYINT UNSIGNED NOT NULL,
wins TINYINT UNSIGNED NOT NULL,
losses TINYINT UNSIGNED NOT NULL,
ties TINYINT UNSIGNED NOT NULL,
minutes MEDIUMINT UNSIGNED NOT NULL,
attributes TINYTEXT NOT NULL,
biography TEXT NOT NULL,
oldstats TEXT NOT NULL,
PRIMARY KEY(goalie_id));

CREATE TABLE skaters (
skater_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(40) NOT NULL,
goals TINYINT UNSIGNED NOT NULL,
assists TINYINT UNSIGNED NOT NULL,
points TINYINT UNSIGNED NOT NULL,
pim SMALLINT UNSIGNED NOT NULL,
plus_minus TINYINT(4) NOT NULL,
attributes TINYTEXT NOT NULL,
biography TEXT NOT NULL,
oldstats TEXT NOT NULL,
PRIMARY KEY(skater_id));
[/code]

As of right now, skaters is empty.

When checking the filled tables goalies and positions, the positions table has 0's in the goalie_id column where there should be an id number.  The goalie_id is correctly created in the goalies table, however.  Since the positions table references the other two, shouldn't its goalie_id column (in this case) automatically have the proper ids for those two rows?

EDIT: would my hosting company changing storing engines from InnoDB to MyISAM be the culprit?

[gluck]

I believe MyISAM doesn't support Foreign keys at this time.

[fenway]

[quote author=gluck link=topic=113495.msg461298#msg461298 date=1162409957]
I believe MyISAM doesn't support Foreign keys at this time.
[/quote]
Correct... but annoyingly, if doesn't throw an error if you include them in the CREATE TABLE statements, which is just stupid.

[KevinM1]

Well, I was able to change the tables to InnoDB, but my hosting won't let me change the actual database, which is still MyISAM.  Despite my hosting telling me that changing just the tables would work, I'm still not seeing the goalie_id's in the positions table (more precisely, everything is still 0 in that column), so I guess they were wrong.

Any ideas on what I can do?

[effigy]

[quote author=Nightslyr link=topic=113495.msg461378#msg461378 date=1162416966]
Well, I was able to change the tables to InnoDB, but my hosting won't let me change the actual database, which is still MyISAM.
[/quote]

How did you change the tables? I thought only [i]tables[/i] had the engine types (MyISAM, InnoDB) and not [i]databases[/i], especially since you can have different engine types within the same database.

[KevinM1]

I was able to force them into InnoDB by putting 'ENGINE=INNODB' at the end of my CREATE statements.

According to phpMyAdmin, my tables are InnoDB, but the database is MyISAM.  Screenshot link:
http://www.nightslyr.com/hockey/dbstructure.jpg

My goalies table is filled out (I used this query so you could see all of the columns).  Screenshot link:
http://www.nightslyr.com/hockey/goalies.gif

But the goalie_id in my positions table still isn't being set.  Screenshot link:
http://www.nightslyr.com/hockey/positions.jpg

And I populated the positions table [b]after[/b] I populated the goalies table, just in case that makes a difference.

[gluck]

very interesting. I think if the default system table type is MyISAM foreign key shouldn't be enforced. Not sure though.

[gluck]

Dude...
What are you trying to do? I don't see you defining the foreign keys?
Another thing that I don't understand is that how would the goalies column have the automatic values? You need to put them in there.
I think I am confused here.

[KevinM1]

As far as I know, the statements in the creation of my positions table that have REFERENCES in them are supposed to create the foreign keys (see my original post for how I created them).

You're right about the second part.  I did some more research and found that I do have to manually put them there.  D'oh!

[gluck]

kool