python config_error ai_generated true

sqlite3.IntegrityError: FOREIGN KEY constraint failed

ID: python/sqlalchemy-sqlite-foreign-key-off

Also available as: JSON · Markdown · 中文
80%Fix Rate
85%Confidence
0Evidence
2024-01-15First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
1.4.x active — — —
2.0.x active — — —

Root Cause

SQLite by default does not enforce foreign key constraints unless PRAGMA foreign_keys=ON is set; but if it's off, referential integrity can be violated, and when turned on, existing bad data causes errors.

generic

中文

SQLite 默认不强制执行外键约束，除非设置 PRAGMA foreign_keys=ON；但如果关闭，引用完整性可能被违反，当打开时，现有错误数据会导致错误。

Workarounds

  1. 90% success 
    Enable foreign key enforcement per connection: from sqlalchemy import event; @event.listens_for(engine, 'connect') def set_sqlite_pragma(dbapi_connection, connection_record): cursor = dbapi_connection.cursor(); cursor.execute('PRAGMA foreign_keys=ON'); cursor.close()
  2. 80% success 
    Clean up orphaned records before enabling FK constraints.

Dead Ends

Common approaches that don't work:

  1. 90% fail

    Data inconsistency persists.

  2. 60% fail

    May lose data and doesn't fix the root cause.