mysql - MySQL 中的多对多关系

Question

我有这个实体关系:

我想保存当前球队和最后球队的信息，同时保存每个球队的不同位置。

我创建这个表:

玩家表:

create table player(

   codPlayer varchar(12) primary key,
   name varchar(30) unique not null,
   codCurrentTeam varchar(12),
   position varchar(20),
   foreign key (codCurrentTeam) references team(codTeam) 
);

团队表:

create table team(

   codTeam varchar(12) primary key,
   name varchar(40) unique not null

);

最后一支球队的表:

create table lastTeam(

    startDate date,
    finishDate date,
    codTeam varchar(12),
    codPlayer varchar(12),
    constraint pkLastTeam primary key (codTeam,codPlayer),
    foreign key (codTeam) references team(codTeam),
    foreign Key (codPlayer) references jugador (codPlayer)
);

职位表:

create table position(

   codPlayer varchar(12),
   codLastTeam varchar(24),
   position varchar(20),
   primary key (codPlayer, codLastTeam),
   foreign key (codPlayer) references jugador(codPlayer),
   foreign key (codLastTeam) references lastTeam(pkLastTeam)

);

我可以创建除position之外的所有表，mysql返回下一个错误:

Error Code: 1215. Cannot add foreign key constraint

也许我的图有一些错误，但我不知道如何实现设计

Answer 1

问题是你没有

在lastTeam t中创建表位置“pkLastTeam”列

codPlayer varchar(12),
   codLastTeam varchar(24),
   position varchar(20),
   primary key (codPlayer, codLastTeam),
   foreign key (codPlayer) references jugador(codPlayer),
   foreign key (codLastTeam) references lastTeam(pkLastTeam)

);