Foreign key not working

Question

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?

Answer 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.