How you modify SQL Lite tables which have many records?

[Bob Sneidar]

What I find more egregious is this statement:

The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

	• The column may not have a PRIMARY KEY or UNIQUE constraint.
	• The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses. 
<snip>

I get the first point, the original table had to already have a PRIMARY KEY and you cannot have 2 columns defined that way. I DON'T get the fact that the new column cannot have the UNIQUE constraint, or that CURRENT_<whatever> is omitted. 

Really, this is so limiting that it may be better to dump your database, recreate the new structure, and then re-import it, or do the second database thingummy, rather than try to modify the schema. 

And I looked up the limits in sqLite. Apparently the original idea was to have no limits, but people found that when you push sqLite to extremes, issues begin to crop up and security can break down. There are however, so far as I have been able to tell, no record limits for sqLite. 

Bob


On Oct 28, 2010, at 9:11 AM, Ruslan Zasukhin wrote:

> 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
> database.
> 
> At this URL
>        http://www.sqlite.org/omitted.html
> 
> 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.
> <snip>