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.