MySQL Error 1005 & 1064 - Foreign Key/Index Issues?

[mtnmchgrl]

I have 4 create table statements that I am having difficulty with. 2 of the 4 have been entered in command line into my mysql database. I am trying to trouble shoot the reason the other 2 won't go in w/out errors.

Per the Forum's Request....here is my stuff:

 

MySQL server version -- 5.1

 

any errors that MySQL returns to the client [from mysql_error()]:

When I enter the stats create table I get this error:

ERROR 1005 (HY000): Can't create table 'baseball.stats' (errno: 150)

 

When I enter the tourneyPlayers create table I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'INDEX

(tourneyPlayerID, FK_playerID) FOREIGN KEY (FK_playerID) REFERENCES player' at line 6

 

the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred]:

My 4 Create Table Statements (first 2 are ok; 2nd two won't go into mysql):

 

CREATE TABLE player (
playerID TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT,
userName VARCHAR (65) NOT NULL,
password VARCHAR (65) NOT NULL,
playerFN VARCHAR (48) NOT NULL,
playerLN VARCHAR (48) NOT NULL,
jerseyNum TINYINT (3) NOT NULL,
contactName VARCHAR (48) NOT NULL,
contactNumber VARCHAR (10) NOT NULL,
position VARCHAR (100) NOT NULL,
bats ENUM(’L’,’R’,’B’) NOT NULL,
PRIMARY KEY (playerID)
)
ENGINE=InnoDB;


CREATE TABLE tournaments (
tourneyID TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT, 
tourneyName VARCHAR(30) NOT NULL,
gameDate date NOT NULL,
PRIMARY KEY (tourneyID)
)
ENGINE=InnoDB;

CREATE TABLE stats (
statID TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT, 
FK_playerID TINYINT (3) NOT NULL,
atBats TINYINT (4) UNSIGNED NOT NULL ,
hits SMALLINT (4)  UNSIGNED NOT NULL ,
walks SMALLINT (4) UNSIGNED NOT NULL ,
RBI SMALLINT (4) UNSIGNED NOT NULL ,
strkOuts SMALLINT (4)  UNSIGNED NOT NULL ,
singles SMALLINT (4) UNSIGNED NOT NULL ,
doubles SMALLINT (4)  UNSIGNED NOT NULL ,
triples SMALLINT (4) UNSIGNED NOT NULL ,
homeRuns SMALLINT (4) UNSIGNED NOT NULL ,
runsScrd SMALLINT (4) UNSIGNED NOT NULL,
fieldersCh SMALLINT (4)  UNSIGNED NOT NULL ,
PRIMARY KEY (statID),
INDEX (statID, FK_playerID),
FOREIGN KEY (FK_playerID) REFERENCES player(playerID) 
ON UPDATE CASCADE
ON DELETE CASCADE
) 
ENGINE = InnoDB;


CREATE TABLE tourneyPlayers (
tourneyPlayerID TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT, 
FK_playerID TINYINT(3) unsigned NOT NULL,
FK_tourneyID TINYINT(3) unsigned NOT NULL,
PRIMARY KEY (tourneyPlayerID)
INDEX (tourneyPlayerID, FK_playerID)
FOREIGN KEY (FK_playerID) REFERENCES player (playerID)
ON UPDATE CASCADE
ON DELETE CASCADE,
INDEX (FK_tourneyID)
FOREIGN KEY (FK_tourneyID) REFERENCES tournaments (tourneyID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=InnoDB;

 

a brief listing of the types of things you've attempted so far

I have tried entering this in a number of ways and keep getting errors. I've switched some things around based on thing I've seen online an in the books. One big difference I seem to keep seeing is that some people put the PK on the line where its first written out. Others put it on the line closer to the bottom before the engine is declared. I know mine issue lies somewhere with the foreign keys but I honestly have no idea. I am also unsure of how I have my index set up? I read that you had to put an index on every field in order to do the FK, so did that also include putting it on every PK also?

 

 

 

[cags]

Your first problem, is probably this...

 

http://www.brainfault.com/2008/02/15/mysql-error-1005-hy000-cant-create-table-tablefrm-errno-150/

 

 

[mtnmchgrl]

Your first problem, is probably this...

 

http://www.brainfault.com/2008/02/15/mysql-error-1005-hy000-cant-create-table-tablefrm-errno-150/

 

I tried that and still cannot get the stats table in. It gives me the 1064 error re: update/delete cascade.

 

 

I got the tourneyPlayers table to go in.... here is how i changed it. I took out indexing and re-worded FK. I don't have update/delete on cascade which might cause a problem.

CREATE TABLE `tourneyPlayers` (
  `tourneyPlayerID` tinyint(3) unsigned NOT NULL auto_increment,
  `FK_playerID` tinyint(3) unsigned default NULL,
  `FK_tourneyID` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`tourneyPlayerID`),
  KEY `FK_playerID` (`FK_playerID`),
  KEY `FK_tourneyID` (`FK_tourneyID`)
) ENGINE=InnoDB;

 

Any other thoughts on the stats?

[Mchl]

It might be easier to create tables without foreign key definitions first, and add these definitions once all tables are in place. Makes debugging easier.