Foreign keys not working - sqlite3.OperationalError: unknown column "user_id" in foreign key definition

Question

sqlite3.OperationalError: unknown column "user_id" in foreign key definition

    # create details table
    details_table = """CREATE TABLE IF NOT EXISTS
    details(
    user_id INTEGER PRIMARY KEY,
    first_name TEXT,
    surname TEXT,
    role TEXT,
    make TEXT,
    model TEXT,
    colour TEXT,
    reg TEXT)"""

    cursor.execute(details_table)

    # create booking table
    booking_table = """CREATE TABLE IF NOT EXISTS
    booking(
    booking_id INTEGER PRIMARY KEY,
    FOREIGN KEY (user_id) REFERENCES details(user_id),
    start_date TEXT,
    expiry_date TEXT)"""
    
    cursor.execute(booking_table)

Any help greatly appreciated :)

Answer 1

You need to declare the column first and then define the foreign key constraint:

CREATE TABLE IF NOT EXISTS booking (
    booking_id INTEGER PRIMARY KEY,
    user_id INTEGER,
----^ your code is missing this declaration
    start_date TEXT,
    expiry_date TEXT,
    FOREIGN KEY (user_id) REFERENCES details(user_id)
)