0

Working on Pset8, Finance. Created a Foreign Key relationship between my portfolio and users table but getting this error when running the application: foreign key mismatch - "portfolio" referencing "users" [SQL: INSERT INTO portfolio(user_id) VALUES(15)]

Yes, there is a user id of 15 in my uses table, just in case you were wondering.

I've attached my users and portfolio tables. I don't see my Foreign Key constraint, but maybe it's not possible in sqlite3? Although, I've read that one can do it.

users table portfolio table

Here's what my code looks like for the imports and buy function:

import os

from datetime import datetime
from cs50 import SQL
from flask import Flask, flash, jsonify, redirect, render_template, request, session
from flask_session import Session
from tempfile import mkdtemp
from werkzeug.exceptions import default_exceptions, HTTPException, InternalServerError
from werkzeug.security import check_password_hash, generate_password_hash

from helpers import apology, login_required, lookup, usd

# Configure application
app = Flask(__name__)

# Ensure templates are auto-reloaded
app.config["TEMPLATES_AUTO_RELOAD"] = True

# Ensure responses aren't cached
@app.after_request
def after_request(response):
    response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
    response.headers["Expires"] = 0
    response.headers["Pragma"] = "no-cache"
    return response

# Custom filter
app.jinja_env.filters["usd"] = usd

# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)

# Configure CS50 Library to use SQLite database
db = SQL("sqlite:///finance.db", foreign_keys=True)
db.execute("PRAGMA foreign_keys = ON")

# Make sure API key is set
if not os.environ.get("API_KEY"):
    raise RuntimeError("API_KEY not set")


@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    return apology("This is Index Page")


@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock"""
    if request.method == "POST":
        stock_symbol = request.form.get("symbol")
        stock_shares = request.form.get("shares")

        if not stock_symbol:
            return apology("Missing Symbol", 400)
        elif not stock_shares:
            return apology("Missing Shares", 400)

        if int(stock_shares) < 1:
            return apology("Shares must be positive", 400)


        rows = db.execute("SELECT cash FROM users WHERE id = :id", id=session['user_id'])
        cash = rows[0]['cash']

        quote = lookup(stock_symbol)
        stock_name = quote['name']
        stock_price = quote['price']

        now = datetime.now()

        total_value = int(stock_shares) * stock_price

        if cash > total_value:
            # buy the stock and insert into portfolio table
            db.execute("INSERT INTO portfolio(stock) VALUES(:stock)",
                        stock=stock_name)

            return redirect("/")
        else:
            return apology("Need more $ to buy!", 400)
    else:
        return render_template("buy.html")

Any help in the right direction would be great!

2
  • That error usually indicates a problem with the schema itself, not a problem with the data. Have you verified that the foreign key itself is correct? Are all table/column names spelled correctly, etc? You could try deleting the foreign key and recreating it. This page might be useful: gist.github.com/geobabbler/8324190 – Cliff B Aug 26 '19 at 2:22
  • Thank you @CliffB. Figured it out! – wolfbagel Aug 26 '19 at 18:14
0

Asked and answered in comments. If this answers your question, please click on the check mark to accept. Otherwise, it'll sit in the unanswered question pool forever. Let's keep up on forum maintenance. ;-)

That error usually indicates a problem with the schema itself, not a problem with the data. Have you verified that the foreign key itself is correct? Are all table/column names spelled correctly, etc? You could try deleting the foreign key and recreating it. This page might be useful: gist.github.com/geobabbler/8324190 – Cliff B 17 hours ago

Thank you @CliffB. Figured it out! – newcoder 1 hour ago

You must log in to answer this question.

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