mysql #1005 errno 150

Question

I'm creating some mysql code with phpmyadmin for a school project and am getting the error "#1005 - Can't create table world_cup.goal (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)".

I can't figure out why I'm getting the error. If I delete the GOAL entity, it still gives the error, but with the CARD entity. In fact, it doesn't like the last 4 entities I created. It has to do with the the foreign key I think.

WHAT I'VE TRIED: making sure that all the FK and the referenced PK are the same datatype. They match perfectly. I can't think of anything else to try.

This is due tomorrow so any help would be greatly appreciated! Thanks!

Here is the code:

DROP DATABASE IF EXISTS WORLD_CUP;
CREATE DATABASE WORLD_CUP;
USE WORLD_CUP;

CREATE TABLE TEAM( 
Cid varchar(2) NOT NULL, 
Continent varchar(20), 
Confederation varchar(20) NOT NULL, 
Population int, 
CName varchar(20) NOT NULL, 
PRIMARY KEY(Cid),
UNIQUE(CName)
); 

 CREATE TABLE PLAYER( 
 PCid varchar(2) NOT NULL, 
 Pno int NOT NULL, 
 Position varchar(20) NOT NULL, 
 Pfname varchar(20) NOT NULL, 
 Plname varchar(20) NOT NULL, 
 Weight int, 
 Height int, 
 Club varchar(20), 
BDayMonth int, 
BDayDay int, 
BDayYear int, 
PJName varchar(22) NOT NULL, 
PRIMARY KEY(PCid, PNo),
FOREIGN KEY (PCid) REFERENCES TEAM(Cid)
); 

CREATE TABLE GAME( 
Gid varchar(2) NOT NULL, 
Score1 int, 
Score2 int, 
Stadium varchar(20), 
Team1Cid varchar(20) NOT NULL, 
Team2Cid varchar(20) NOT NULL, 
GMonth int, 
GDay int, 
GYear int, 
GType char(1) NOT NULL, 
KOGSubtype char(1),
PRIMARY KEY(Gid, Team1Cid, Team2Cid),
FOREIGN KEY(Team1Cid) REFERENCES TEAM(Cid),
FOREIGN KEY(Team2Cid) REFERENCES TEAM(Cid)
); 

CREATE TABLE STADIUM( 
Sid varchar(2) NOT NULL, 
Sname varchar(20) NOT NULL, 
Capacity int, 
City varchar(20), 
PRIMARY KEY(Sid)
); 

CREATE TABLE GOAL( 
GPno int NOT NULL, 
GMinute varchar(6) NOT NULL, 
GoalType char NOT NULL, 
GGid varchar(2) NOT NULL, 
PRIMARY KEY(GPno, GGid, GMinute) ,
FOREIGN KEY (GPno) REFERENCES PLAYER(Pno),
FOREIGN KEY (GGid) REFERENCES GAME(Gid)
); 

CREATE TABLE CARD( 
CPno int NOT NULL, 
CMinute varchar(6) NOT NULL, 
Color char(1) NOT NULL, 
CGid varchar(2) NOT NULL, 
PRIMARY KEY(CPno, CGid, CMinute) ,
FOREIGN KEY(CPno) REFERENCES PLAYER(Pno),
FOREIGN KEY(CGid) REFERENCES GAME(Gid)
); 

CREATE TABLE SUBSTITUTE( 
PInNo int NOT NULL,
POutNo int NOT NULL, 
SMinute varchar(6) NOT NULL, 
SGid varchar(2) NOT NULL,
SCid varchar(2) NOT NULL,
PRIMARY KEY(PInNo, POutNo, SMinute, SGid, SCid),
FOREIGN KEY (PInNo) References PLAYER(Pno),
 FOREIGN KEY (POutNo) References PLAYER(Pno),
 FOREIGN KEY (SGid) References GAME(Gid),
 FOREIGN KEY (SCid) References TEAM(Cid)
);

CREATE TABLE STARTINGLINEUP( 
SPno int NOT NULL,
PCid varchar(2) NOT NULL,
PGid varchar(2) NOT NULL,
PRIMARY KEY(SPno, PCid, PGid),
FOREIGN KEY (SPno) REFERENCES PLAYER(Pno),
FOREIGN KEY(PCid) REFERENCES TEAM(Cid),
FOREIGN KEY (PGid) REFERENCES GAME(Gid)
);

Answer 1

It's because the primary key on PLAYER is composite, so the foreign key that points to it has to be composite too.

My version of GOAL - note addition of GPCid and its inclusion in the foreign key:

CREATE TABLE GOAL( 
GPCid varchar(2) not null,
GPno int NOT NULL, 
GMinute varchar(6) NOT NULL, 
GoalType char NOT NULL, 
GGid varchar(2) NOT NULL, 
PRIMARY KEY(GPno, GGid, GMinute) ,
FOREIGN KEY (GPCid,GPno) REFERENCES PLAYER(PCid,Pno),
FOREIGN KEY (GGid) REFERENCES GAME(Gid)
); 

And similarly for CARD etc.