Jump to content

Foreign key problems


KevinM1

Recommended Posts

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?
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.