Peewee操作SQLite触发FOREIGN KEY constraint failed错误的原因与解决

我使用DB Browser图形客户端查看表结构时发现，初始代码生成的candles表中timeframe_id和symbol_id字段为INTEGER类型，而非sqlite3客户端执行.schema命令输出的VARCHAR类型：

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>

发现该问题后，我补充学习了外键相关知识，同时梳理了peewee的外键实现逻辑，明确了之前对外键概念的认知误区，也读懂了官方文档的相关说明。

以下是修改后可正常运行的代码：

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'}
%

我尝试通过peewee向sqlite表插入带外键字段的数据，持续触发FOREIGN KEY constraint failed报错，但不清楚问题根源。

我是数据库开发新手，按照外键的基础概念，只要Candles.symbol和Candles.timeframe的值在父表Symbols.name和Timeframes.name中已经存在，就应该可以正常插入。

外键约束可以防止无效数据插入外键列，因为外键值必须是父表对应列中已有的值。

我在示例代码中添加了日志确认预期的数值均已存在，依然报错，希望获得相关指导。

报错代码如下：

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
%

内容的提问来源于stack exchange，提问作者Jason