I am starting a project involving a small database and I am considering to use SQLite. If I create a table and I defined one of the columns as text, but all the values stored are integers - is there a way to change the data type of a column? I am using SQLite Manager and I can't find a function that allows me to do that. Can I do it using a SQL command, or is it a restriction of SQLite? Probably, even with this restriction, one can find some way around and create a new table with the required column types and then import data into it.
Regards, Nick
SQLite does not fully support ALTER TABLE statements. The only thing you can do is rename a table and/or add columns. If you want to rename a column, your best option is to create a new table with the new columns datatype/names, and to drop the old table in order to rename the new one.
Lets say, you have a table and need to rename "field-1" to "field-2": First ==>> rename the old table:
ALTER TABLE original RENAME TO tmp;
Now create the new table based on the old table but with the updated column name:
==>> create a table with the updated columns
CREATE TABLE original(
field_a INT
, field_b INT
);
Then copy the contents across from the original table.
INSERT INTO origignal(field_a, field_b)
SELECT field_a, field_b
FROM tmp;
Lastly, drop the old table.
DROP TABLE tmp;