0

I'm facing a Runtime Error in /buy when I try to insert information about the stock into the holdings table. Here is my code for /buy:

        # Update holdings table
        db.execute("INSERT INTO holdings (user_id, symbol, price, shares) VALUES (?, ?, ?, ?)", int(session["user_id"]), request.form.get("symbol"), price, int(request.form.get("shares")))

When I run the query separately on sqlite3, it runs successfully without errors. But when I run the above code using flask run, here is the error I receive:

RuntimeError: foreign key mismatch - "holdings" referencing "users"

And here is my table schema:

CREATE TABLE holdings (
user_id INTEGER NOT NULL,
symbol TEXT NOT NULL,
price NUMERIC NOT NULL, shares INTEGER,
FOREIGN KEY (user_id)
REFERENCES users (user_id)
);

I don't know if the problem is with my code, or with the schema of the table. Does anyone have any idea what the solution to this problem could be??

I realized that the problem was with the schema of the table, in REFERENCES users(user_id), it should be users(id) because that is the original column name in the users table. I also added the CONSTRAINT line to the schema.

CONSTRAINT fk_users
FOREIGN KEY (user_id)
REFERENCES users(id)

1 Answer 1

1

First question: Did you confirm that the userid in the insert statement for the row you're trying to insert into the holdings table actually exists in the userid table?

If not, you get a runtime error. That's what foreign keys do. They don't cause an insert into another table, they prevent an insert into the current table if the key data doesn't already exist in the foreign table.

1
  • Thanks for pointing me in the right direction! I eventually realized that the problem was in the schema of my table. When I changed REFERENCES users(user_id) to users(id), It was successful! Nov 6, 2022 at 13:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .