Jupyter Lab中创建MySQL外键失败问题求助

错误根源 #

错误提示明确指向列名不匹配：你在results表的外键定义里，引用away_team表的列名写错了。away_team表的主键列是id_team_away，但外键语句里写的是id_away_team，两者不一致导致约束创建失败。

修正后的代码片段 #

以下是修正外键引用并优化部分参数后的完整代码：

# 创建away_team表
sql_query = """CREATE TABLE away_team(
               id_team_away  INT NOT NULL,
               away_team_name  VARCHAR(45),
               PRIMARY KEY (id_team_away)
               )"""
conn.execute(sql_query)
# 用append避免覆盖手动创建的带主键的表，index=False不导入DataFrame索引列
df_away_team.to_sql('away_team', conn, if_exists='append', index=False)

# 创建home_team表
df_home_team = df[['home_team']]
sql_query = '''DROP TABLE IF EXISTS home_team'''
conn.execute(sql_query)
sql_query = '''CREATE TABLE home_team(
   id_home_team  INT NOT NULL,
   home_team_name  VARCHAR(45),
   PRIMARY KEY (id_home_team)
   )'''
conn.execute(sql_query)
df_home_team.to_sql('home_team', conn, if_exists='append', index=False)

# 创建tournaments表（修正字段类型：赛事名称应为字符串而非INT）
dftournament = df[['tournament']]
sql_query = '''DROP TABLE IF EXISTS tournaments'''
conn.execute(sql_query)
sql_query = '''CREATE TABLE tournaments(
   id_tournaments  INT NOT NULL,
   tournament  VARCHAR(100),
   PRIMARY KEY (id_tournaments)
   )'''
conn.execute(sql_query)
dftournament.to_sql('tournaments', conn, if_exists='append', index=False)

# 创建results表（修正外键引用的列名）
sql_query = '''DROP TABLE IF EXISTS results'''
conn.execute(sql_query)
sql_query = '''CREATE TABLE results(
   id_results  INT NOT NULL,
   home_team_fifa_rank BIGINT,
   home_team_goalkeeper_score DOUBLE,
   home_team_mean_offense_score DOUBLE, 
   home_team_mean_midfield_score DOUBLE,
   away_team_fifa_rank BIGINT,
   away_team_goalkeeper_score DOUBLE,
   away_team_mean_defense_score DOUBLE,
   away_team_mean_midfield_score DOUBLE,
   shoot_out BIGINT,
   date DATETIME,
   result INT,
   home_team_id  INT NOT NULL,
   away_team_id  INT NOT NULL,
   tournaments_id  INT NOT NULL,
     
   PRIMARY KEY (id_results),
   -- 修正此处列名：对应away_team表的主键id_team_away
   FOREIGN KEY (away_team_id) REFERENCES away_team (id_team_away),
   FOREIGN KEY (home_team_id) REFERENCES home_team (id_home_team),
   FOREIGN KEY (tournaments_id) REFERENCES tournaments (id_tournaments)
   )'''
conn.execute(sql_query)
dfresult.to_sql('results', conn, if_exists='append', index=False)

额外优化提示 #

1. if_exists参数：原代码用replace会删除手动创建的带约束的表，自动生成无约束的新表，改用append可保留手动设置的主键/外键结构。
2. index参数：设置index=False避免将DataFrame的索引作为多余列导入数据库。
3. 字段类型修正：tournaments表的tournament字段原定义为INT(100)，如果是赛事名称类字符串，改为VARCHAR(100)更合理。

内容的提问来源于stack exchange，提问作者Andy