3 ms·
Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create t
by simonw 2y ago
Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create the new table, copy the data across and then atomically swap the table names before you commit: https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter
My favourite feature of my sqlite-utils CLI tool is the "transform"
command which implements this pattern for you (also available as a Python library method): https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
- silvestrov 2y agoThis does not work (easily) when other tables have "foreign key" constraints to the table. You need to manually recreate them.
- simonw 2y agoYeah, my solution doesn't automatically handle that (yet). That's why it provides a --sql option - if it doesn't entirely handle your particular case you can instead get it to generate SQL for you without executing it, so you can make modifications you need before running it.