4 ms·

This got me hoping for an sqlite where adding foreign keys didn’t require making a whole new table.

• contingencies 4y ago
  Doesn't this work? cp db.sqlite3 backup.sqlite3 echo .dump tablename |sqlite3 db.sqlite3 >old.sql vi old.sql # make changes, ensure pragma FOREIGN_KEYS=off; drop table before insert sqlite3 db.sqlite3 <old.sql
• simonw 4y ago
  You don't need to create a new table to add foreign keys - you can do it by setting PRAGMA writable_schema = 1, then updating the table definition directly in the sqlite_master table to include the foreign key constraints. This sounds terrifyingly dangerous, so you should absolutely take a backup first - but I've found it to work just fine in practice. Here's where I implement that in my sqlite-utils Python library and CLI tool: https://github.com/simonw/sqlite-utils/blob/2d84577202e227670ad434fcf3dd9786a2df0819/sqlite_utils/db.py#L1013-L1027 https://github.com/simonw/sqlite-utils/blob/2d84577202e22767...