0

I'm having problems with the use of foreign key:

  • For pset7 - finance I have created a table portfolio, per the following command:

    CREATE TABLE finance.portfolio (transactionid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER NOT NULL, stock TEXT NOT NULL, symbol TEXT NOT NULL, price NUMERIC NOT NULL, numshares INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id));

  • I have enabled the use of foreign keys via the following:

    db = SQL("sqlite:///finance.db")

    db.execute("PRAGMA foreign_keys = ON")

  • Currently my 'users' table has one entry, where 'id' = 2

  • As a test I want to see if the foreign key prohibits me to add a row to the portfolio table, where the foreign key 'user_id' does not map to a value in users(id), so added this line of code:

    db.execute("INSERT INTO portfolio (user_id, stock, symbol, price, numshares) VALUES(3, :stock, :symbol, :price, :numshares)", stock = stockname, symbol = stocksymbol, price = stockprice, numshares = shares)

  • Since this will add a row with user_id = 3 to the table, and this does not map to a record in the users table, I expect a failure due to the foreign key to insert this row. But it doesn't, the row is added to the portfolio table.

Does anybody know why this might occur?

2
  • Is the constraint enforced when you go via sqlite3?
    – Blauelf
    Nov 22 '18 at 14:44
  • @Blauelf: yes, then the constraint is enforced. When I first enable foreign keys via the PRAGMA command and then do the INSERT INTO command, it fails due to 'FOREIGN KEY constraint failed'
    – JJuice
    Nov 22 '18 at 15:21
1

After reading the source of the library used at https://github.com/cs50/python-cs50/blob/develop/src/cs50/sql.py#L36-L44, I found the correct usage is not sending PRAGMA foreign_keys=ON, which seems to do nothing, but to connect via

db = SQL("sqlite:///finance.db", foreign_keys=True)

After doing this, it seems to work for me, an INSERT statement not allowed due to constraints silently fails, returning None.

You must log in to answer this question.

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