Foreign key error in 'creating and designing database project'

Hi, while working on the database creation project I happen to frequently encounter the ‘OperationalError: foreign key mismatch - “game” referencing “park”’ error. I double checked my code against the solution code as well but the error keeps hitting me. Any help would be appreciated!

Code

c11 = ‘’‘CREATE TABLE IF NOT EXISTS game (
game_id TEXT PRIMARY KEY,
date TEXT,
number_of_game INTEGER,
day_of_week TEXT,
park_id TEXT,
length_outs INTEGER,
day BOOLEAN,
completion TEXT,
forefeit TEXT,
protest TEXT,
attendance TEXT,
length_minutes INTEGER,
additional_info TEXT,
acquisition_info TEXT,
FOREIGN KEY (park_id) REFERENCES park(park_id)
)
;
‘’’

c12 = ‘’‘INSERT OR IGNORE INTO game
select
game_id,
date,
number_of_game,
day_of_week,
park_id,
length_outs,
CASE WHEN
day_night = ‘D’ THEN ‘1’
WHEN day_night = ‘N’ THEN ‘0’
END as day,
completion,
forefeit,
protest,
attendance,
length_minutes,
additional_info,
acquisition_info
FROM game_log;
‘’’

q6 = ‘’‘SELECT * FROM game LIMIT 5;’’’

run_command(c11)
run_command(c12)
run_query(q6)

Error

OperationalError Traceback (most recent call last)
in
44
45 run_command(c11)
—> 46 run_command(c12)
47 run_query(q6)

in run_command©
9 conn.execute(‘PRAGMA foreign_keys = ON;’)
10 conn.isolation_level = None
—> 11 conn.execute©
12
13 def show_tables():

OperationalError: foreign key mismatch - “game” referencing “park”

I googled the error and found this stack overflow answer. It suggests that the problem may be either with the creation or the data of the park table.

I’d start by making sure that park_id in the park table is a primary key, and that the values in it are unique.

Another thing to check is that all values for park_id in game are present in park, there’s a chance that could be the source of the error also.

I hope this helps,

2 Likes

I’ll try your approach
Thanks :grinning: