如何将tblIndividuals表的competitorID字段同步到tblIndividualEvents表

让我帮你梳理下代码里的问题，这几个关键点导致了tblIndividualEvents表的competitorID字段没正常显示（甚至表可能都没正确创建）：

1. 创建表的SQL语法错误 #

你在individual_events()里的CREATE TABLE语句末尾少了闭合的双引号和括号，这会直接导致SQL执行失败，表根本无法创建：

# 原错误代码（末尾缺少")"）
cursor.execute("""CREATE TABLE tblIndividualEvents (competitorID INTEGER, FOREIGN KEY(competitorID) REFERENCES tblIndividuals(competitorID)) """

修正后要确保语法完整，还可以加上IF NOT EXISTS避免重复创建报错：

cursor.execute("""
    CREATE TABLE IF NOT EXISTS tblIndividualEvents (
        competitorID INTEGER,
        FOREIGN KEY(competitorID) REFERENCES tblIndividuals(competitorID)
    )
""")

2. SQLite外键约束默认未启用 #

SQLite默认是关闭外键约束的，即使你写了外键语句也不会生效，需要在建立连接后手动开启：

def create_database():
    global cursor
    global connection
    connection = sqlite3.connect("Tournament.db")
    # 启用外键约束
    connection.execute("PRAGMA foreign_keys = ON")
    cursor = connection.cursor()

3. 缺少数据同步逻辑 #

你只创建了tblIndividualEvents表，但没有把tblIndividuals里已有的competitorID插入到新表中，所以表是空的，看起来像字段没显示。需要添加同步语句：

def individual_events():
    # 先正确创建表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS tblIndividualEvents (
            competitorID INTEGER,
            FOREIGN KEY(competitorID) REFERENCES tblIndividuals(competitorID)
        )
    """)
    # 同步tblIndividuals中的competitorID到新表
    cursor.execute("INSERT INTO tblIndividualEvents (competitorID) SELECT competitorID FROM tblIndividuals")
    connection.commit()
    print("已成功创建表并同步数据")

4. 额外优化：competitorID重复检查逻辑 #

你当前用本地列表检查重复的逻辑有问题（第一次生成的ID肯定会被加入列表，然后触发重复检查），应该直接从数据库查询已存在的ID，确保不重复：

def add_entry():
    type_of_entry = input("Individual or team entry? ")
    # 从数据库获取已使用的ID
    cursor.execute("SELECT competitorID FROM tblIndividuals")
    competitor_numbers_used = [row[0] for row in cursor.fetchall()]
    while type_of_entry.lower() == "individual":
        competitorID = random.randint(1,20)
        # 循环生成直到拿到未使用的ID
        while competitorID in competitor_numbers_used:
            competitorID = random.randint(1,20)
        competitor_numbers_used.append(competitorID)
        title = input ("Enter your title: ")
        firstName = input("Enter your first name: ")
        surname = input("Enter surname: ")
        cursor.execute("INSERT INTO tblIndividuals VALUES (?,?,?,?)", (competitorID, title, firstName, surname))
        connection.commit()
        type_of_entry = input("Individual or team entry? (Type end to go back to the menu) ")
        if type_of_entry.lower() == "end":
            menu()

把这些修改应用到你的代码里，就能正常创建表并同步competitorID字段了。

内容的提问来源于stack exchange，提问作者S O'Connor