Hacker News new | ask | show | jobs
by cordite 1461 days ago
This got me hoping for an sqlite where adding foreign keys didn’t require making a whole new table.
2 comments
simonw 1461 days 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/2d84577202e22767...

link
contingencies 1461 days 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
link