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?