Peewee: Not understanding concept of why code is generating "FOREIGN KEY constraint failed"

Question

EDIT

I had a closer look at the schema using DB Browser (graphical client) and noticed that the schema showed timeframe_id and symbol_id as INTEGER instead of VARCHAR which is what .schema produces in sqlite3 client output:

sqlite> .schema candles
CREATE TABLE IF NOT EXISTS "candles" ("id" INTEGER NOT NULL PRIMARY KEY, "timeframe_id" VARCHAR(255) NOT NULL, "symbol_id" VARCHAR(255) NOT NULL, "text" VARCHAR(255) NOT NULL, FOREIGN KEY ("timeframe_id") REFERENCES "timeframes" ("name"), FOREIGN KEY ("symbol_id") REFERENCES "symbols" ("name"));
CREATE INDEX "candles_timeframe_id" ON "candles" ("timeframe_id");
CREATE INDEX "candles_symbol_id" ON "candles" ("symbol_id");
sqlite>

After noticing that, and absorbing a bit more content on foreign keys , I discovered a few flaw in my understanding of Foreign Keys as a concept, and also gained some clarity on the peewee implementation that makes the documentation more clear.

Below is a modified version of the original code which works as intended.

from collections import namedtuple
from peewee import *
from playhouse.sqlite_ext import SqliteExtDatabase

db = SqliteExtDatabase('test.db', pragmas={'foreign_keys': 1, 'journal_mode': 'wal'})

class BaseModel(Model):
        class Meta:
            database = db

class Symbols(BaseModel):
    name = CharField(unique=True)

class Timeframes(BaseModel):
    name = CharField(unique=True)

class Candles(BaseModel):
    timeframe = ForeignKeyField(Timeframes, field='name') 
    symbol = ForeignKeyField(Symbols, field='name')

db.create_tables([Symbols, Timeframes, Candles])

Symbols.insert(name='ABC').on_conflict(action='IGNORE').execute()
Timeframes.insert(name='1m').on_conflict(action='IGNORE').execute()
print(f"symbols: {Symbols.select().dicts().get()}")
print(f"timeframes: {Timeframes.select().dicts().get()}")
try:
    Candles.insert(timeframe = '1m', symbol = 'ABC').execute()
except Exception as e:
    print(f"Exception: {e}")
print(f"candles: {Candles.select().dicts().get()}")

% test.py
symbols: {'id': 1, 'name': 'ABC'}
timeframes: {'id': 1, 'name': '1m'}
candles: {'id': 1, 'timeframe': '1m', 'symbol': 'ABC'}
%

ORIGINAL

I'm trying to insert some data into an sqlite table via peewee using foreign key fields. I keep getting FOREIGN KEY constraint failed errors but I'm unsure why.

I'm new to database programming, but conceptually, I understand that this should work because Symbols.name and Timeframes.name already contain the fields that Candles.symbol and Candles.timeframe are using as foreign keys, respectively.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

I've added logging to the sample code to attempt to show my work as I've been working through this, to ensure all of the values that I expect to see are present.

I'm obviously mis-understanding something. Any insight would be much appreciated.

from peewee import *
from playhouse.sqlite_ext import SqliteExtDatabase

db = SqliteExtDatabase('test.db', pragmas={'foreign_keys': 1, 'journal_mode': 'wal'})

class BaseModel(Model):
        class Meta:
            database = db

class Symbols(BaseModel):
    name = CharField(unique=True)

class Timeframes(BaseModel):
    name = CharField(unique=True)

class Candles(BaseModel):
    timeframe = ForeignKeyField(Timeframes) 
    symbol = ForeignKeyField(Symbols)

db.create_tables([Symbols, Timeframes, Candles])

Symbols.insert(name='ABC').on_conflict(action='IGNORE').execute()
Timeframes.insert(name='1m').on_conflict(action='IGNORE').execute()
print(f"symbols: {Symbols.select().dicts().get()}")
print(f"timeframes: {Timeframes.select().dicts().get()}")
try:
    q = Candles.insert(timeframe = '1m', symbol = 'ABC').execute()
except Exception as e:
    print(f"Exception: {e}")

% test.py
symbols: {'id': 1, 'name': 'ABC'}
timeframes: {'id': 1, 'name': '1m'}
Exception: FOREIGN KEY constraint failed
%

Answer 1

By default peewee foreign-keys will point to the related model's primary key (which is an autoincrement integer by default). So your original example attempts to use 'ABC' for the symbol, but instead it should have been the "id" of the symbol whose name is "ABC". In your updated example, you explicitly specified the "name" column as the FK target, at which point everything works:

class Candles(BaseModel):
    timeframe = ForeignKeyField(Timeframes, field='name')
    symbol = ForeignKeyField(Symbols, field='name')

db.create_tables([Symbols, Timeframes, Candles])

Symbols.insert(name='ABC').execute()
Timeframes.insert(name='1m').execute()
# Works fine:
q = Candles.insert({'timeframe': '1m', 'symbol': 'ABC'}).execute()

Another way to achieve similar, is to use the symbol name/timeframe name as the primary key:

class Symbols(BaseModel):
    name = CharField(primary_key=True)

class Timeframes(BaseModel):
    name = CharField(primary_key=True)

class Candles(BaseModel):
    timeframe = ForeignKeyField(Timeframes)
    symbol = ForeignKeyField(Symbols)

db.create_tables([Symbols, Timeframes, Candles])

Symbols.insert(name='ABC').execute()
Timeframes.insert(name='1m').execute()
print(f"symbols: {Symbols.select().dicts().get()}")
print(f"timeframes: {Timeframes.select().dicts().get()}")
q = Candles.insert({'timeframe': '1m', 'symbol': 'ABC'}).execute()

Note that there is no "id" column present when using the name as the PK:

symbols: {'name': 'ABC'}
timeframes: {'name': '1m'}