How you modify SQL Lite tables which have many records?

Ruslan Zasukhin ruslan_zasukhin at
Thu Oct 28 12:11:52 EDT 2010

Hi Guys,

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:
> CREATE TEMPORARY TABLE t1_backup(a,b);
> INSERT INTO t1_backup SELECT a,b FROM 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 ...

Best regards,

Ruslan Zasukhin
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 mailing list