Foreign keys Sqlite3 Python3

Question

I have been having some trouble with my understanding of how foreign keys work in sqlite3.

Im trying to get the userid (james) in one table userstuff to appear as foreign key in my otherstuff table. Yet when I query it returns None.

So far I have tried:

1. Enabling foreign key support
2. Rewriting a test script (that is being discussed here) to isolate issue
3. I have re-written some code after finding issues in how I had initially written it
4. After some research I have come across joins but I do not think this is the solution as my current query is an alternative to joins as far as I am aware

Code

import sqlite3 as sq

class DATAB:

def __init__(self):
 self.conn = sq.connect("Atest.db")
 self.conn.execute("pragma foreign_keys")
 self.c = self.conn.cursor()
 self.createtable()
 self.defaultdata()
 self.show_details() # NOTE DEFAULT DATA ALREADY RAN
def createtable(self):
 self.c.execute("CREATE TABLE IF NOT EXISTS userstuff("
 "userid TEXT NOT NULL PRIMARY KEY,"
 " password TEXT)")
 self.c.execute("CREATE TABLE IF NOT EXISTS otherstuff("
 "anotherid TEXT NOT NULL PRIMARY KEY,"
 "password TEXT,"
 "user_id TEXT REFERENCES userstuff(userid))")
def defaultdata(self):
 self.c.execute("INSERT INTO userstuff (userid, password) VALUES (?, ?)", ('james', 'password'))
 self.c.execute("INSERT INTO otherstuff (anotherid, password, user_id) VALUES (?, ?, ?)",('aname', 'password', 'james'))
 self.conn.commit()
def show_details(self):
 self.c.execute("SELECT user_id FROM otherstuff, userstuff WHERE userstuff.userid=james AND userstuff.userid=otherstuff.user_id")
 print(self.c.fetchall())
 self.conn.commit()

-----NOTE CODE BELOW THIS IS FROM NEW FILE---------

import test2 as ts
x = ts.DATAB()

Many thanks

Answer 1

A foreign key constraint is just that, a constraint. This means that it prevents you from inserting data that would violate the constraint; in this case, it would prevent you from inserting a non-NULL user_id value that does not exist in the parent table.

By default, foreign key constraints allow NULL values. If you want to prevent userstuff rows without a parent row, add a NOT NULL constraint to the user_id column.

In any case, a constraint does not magically generate data (and the database cannot know which ID you want). If you want to reference a specific row of the parent table, you have to insert its ID.

• OnePiece: So I tried adding the following line of code self.c.execute("INSERT INTO otherstuff (user_id) VALUES (?)",('james')) but I now get a number of binding error. Am I right in saying the issue with my code is I need to give the otherstuff table the userid of james to use as a foreign key ?
• CL.: ('james') is a string with five elements; a one-element tuple would be ('james',).
• OnePiece: Okay after querying the data again I now get sqlite3.OperationalError: no such column: james, Yet this should not occur since there is now data in all columns. I updated the main code with changes and still dont see my problem
• CL.: Two INSERTs results in two rows in the otherstuff table. And the error message does not come from any code you've shown.
• OnePiece: Deleted table , edited code above and ran as is, resulted in following error...sqlite3.IntegrityError: NOT NULL constraint failed: otherstuff.anotherid, Yet from the code we can see I set it to aname