mysql #1005 错误号 150

Question

我正在使用 phpmyadmin 为学校项目创建一些 mysql 代码，但收到错误“#1005 - 无法创建表 world_cup。goal(errno: 150 “外键约束格式不正确”)(详细信息...)”。

我不明白为什么会收到错误。如果我删除 GOAL 实体，它仍然会给出错误，但会出现 CARD 实体。事实上，它不喜欢我创建的最后 4 个实体。我认为这与外键有关。

我尝试过的:确保所有 FK 和引用的 PK 都是相同的数据类型。他们完美匹配。我想不出还有什么可以尝试的。

此邮件将于明天到期，因此我们将不胜感激!谢谢!

这是代码:

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

因为PLAYER上的主键是复合主键，所以指向它的外键也必须是复合主键。

我的 GOAL 版本 - 请注意添加 GPCid 并将其包含在外键中:

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)
); 

对于 CARD 等也类似。