How you modify SQL Lite tables which have many records?
ruslan_zasukhin at valentina-db.com
Thu Oct 28 12:11:52 EDT 2010
One developer, which try now implement some GUI around SQL Lite have touch
me with wonder that SQL Lite do not have any way to modify schema of
At this URL
You can read that
> Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are
> supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER
> COLUMN, ADD CONSTRAINT, and so forth are omitted.
And at this URL
Is suggested "solution"
> SQLite has limited ALTER TABLE support that you can use to add a column to the
> end of a table or to change the name of a table. If you want to make more
> complex changes in the structure of a table, you will have to recreate the
> table. You can save existing data to a temporary table, drop the old table,
> create the new table, then copy the data back in from the temporary table.
> For example, suppose you have a table named "t1" with columns names "a", "b",
> and "c" and that you want to delete column "c" from this table. The following
> steps illustrate how this could be done:
> BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE t1_backup(a,b);
> INSERT INTO t1_backup SELECT a,b FROM t1;
> DROP TABLE t1;
> CREATE TABLE t1(a,b);
> INSERT INTO t1 SELECT a,b FROM t1_backup;
> DROP TABLE t1_backup;
I wonder if this is true? And there is no any other more effective way ??
COPY the whole table to just add/rename column?
Or just add Foreign Key ?
And if table has million record? 100MB size? 1GB size ? More?
This is of course not a problem, if table has small records count ...
But still ... Somehow not comfortable as for me ...
Good rule of good developer says:
never believe your app will have small number of items ...
VP Engineering and New Technology
Paradigma Software, Inc
Valentina - Joining Worlds of Information
[I feel the need: the need for speed]
More information about the Use-livecode