Thursday, March 26, 2009

Workaround for ALTER TABLE limitations

As you know the ALTER TABLE sql command built-into sqlite has some limitations (sqlite is the database engine behind REALSQLDatabase and REAL SQL Server).
Basically what you can do with ALTER TABLE is just rename a table or add a column.

As a quick workaround you can use the SQL language.
For example if you have a table like:
CREATE TABLE table1 (field1 INTEGER, field2 INTEGER, field3 INTEGER)

and if you want to change field2 to TEXT and remove field3, you can write the following SQL commands:

CREATE TABLE table1_temp (field1 INTEGER, field2 TEXT);
INSERT INTO table1_temp (field1, field2) SELECT field1, field2 FROM table1;
DROP TABLE table1;
ALTER TABLE table1_temp RENAME TO table1;

With these 4 commands you can CHANGE (manually) every table definitions.
Please note that in this way you need to re-create indexes, views and triggers associated to table1.