2 ms·
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
by rogerbinns 14y ago
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.
- wvenable 14y agoI'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...
- premchai21 14y agoBut, 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.
- wvenable 14y agoDoes that actually work for dropping and renaming tables? I thought I tried that.
- rogerbinns 14y agoIn any event you can get the foreign keys from sqlite_master, delete them during the reorganisation and then put them back afterwards.
- premchai21 14y agoIt does in 3.7.13 (and presumably older versions, but I don't have a solid record of this immediately available).