Hacker News Reader

Comment by wvenable | Hacker News Reader

The thing that really bugs me with SQLite3 is the inability to modify tables. It's not much of a problem unless you have foreign key constraints and then it's a nightmare. Ultimately I resorted to just recreating the entire database from a script every time I had to make a change. If this is "fixed" in SQLite4 then I'm sold.

I'm surprised that there aren't any real binary integers stored anymore but I can see how that would reduce complexity compared to how numbers were handled in SQLite3.

View on HN

You can alter tables to add columns. Doing more complex modifications would require SQLite internally to do pretty much what you do - recreating a new database from existing content.

I'd love if it just created a new table, moved the data over, and dropped the original table while maintaining the foreign key relationships. That would be perfect.

If you have foreign key relationships referencing the table, you can't drop the table. You can't remove the foreign key relationships without modifying the related table. But you can't modify that related table, you can only drop it. But you can't drop that table because of other foreign key relationships...

But, last I checked, you could PRAGMA foreign_keys=off temporarily, which would also inhibit the automatic handling of foreign key constraints in other tables when the target table is renamed or dropped. Then you can create a new table, populate it, drop the old one, rename the new one to the old name, and turn foreign_keys back on, and the constraints will now target the new table.

Does that actually work for dropping and renaming tables? I thought I tried that.

In any event you can get the foreign keys from sqlite_master, delete them during the reorganisation and then put them back afterwards.

It does in 3.7.13 (and presumably older versions, but I don't have a solid record of this immediately available).

Reply on news.ycombinator.com