41

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

Nick_F
  • 1,103
  • 2
  • 13
  • 30
  • 1
    there is no straight way to use `alter table` to change column type in SqLite http://www.sqlite.org/lang_altertable.html. What you can do is rename the table, create a new table with new structure and copy data to the new one. – bansi Jan 18 '14 at 02:52
  • +1 great question , that is why i dont use sqllite due to the limitations of its functionalities. – z atef Jan 18 '14 at 03:14
  • 1
    Somebody gave another answer and he deleted it soon after that. It was just the answer I was looking for. With SQLite Manager, I can select the table, view its structure and right click on the column name and choose "Edit". I can change the data type, but I get the warning this is a potentially dangerous operation - as long as we backup the table or the database, I don't see it as a great risk. – Nick_F Jan 18 '14 at 03:15
  • I did saw that , but not sure if it will work – z atef Jan 18 '14 at 03:19
  • NullSoulException - I tried it and it works - at least for a simple table with only a few rows. – Nick_F Jan 18 '14 at 04:42

4 Answers4

70

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;
z atef
  • 7,138
  • 3
  • 55
  • 50
  • 16
    Also, do this all in one transaction. – Donal Fellows Jan 19 '14 at 12:40
  • 6
    You can do it in the other order: create new table with temporary name, copy everything to new table, then drop old table and rename new table to its original name. Then you only need a short-lived transaction around the drop-rename actions at the end instead of a long transaction around everything. – thomasrutter Sep 08 '16 at 04:26
  • 2
    This is incorrect order. Look at this! https://sqlite.org/lang_altertable.html – Maxim Petlyuk Oct 23 '19 at 14:31
5

No, there is no "quick" way to do this with SQLite. Not like you can with MySQL. You will have to drop the table, then re-add it.

Another thing to keep in mind is that SQLite is pretty flexible about the type of data that you can put into each section. Here is a link that describes the datatypes, and how they work: http://www.sqlite.org/datatype3.html

Another option might be to use MySQL if the feature you are speaking of is a big deal to you. It's still free and is great for small projects.

BlackHatSamurai
  • 23,275
  • 22
  • 95
  • 156
  • Thank you Blaine. They've done a nice thing with the dynamic data types, rather than constraining a value to a static type, they match the container to the data you want to store. I probably don't need to change the data type too often, only during development. I was taking it for granted and I was surprised it't not so straightforward. – Nick_F Jan 18 '14 at 04:41
  • I know what you mean. I had to do the same thing, but learned the hard way. Good luck with it. – BlackHatSamurai Jan 18 '14 at 04:44
-2

Alter table in SqliteManger

Steps

  1. Open database in sqlite manage
  2. double click on table name
  3. will popup alter window.
chavanNil
  • 161
  • 2
  • 4
-3

With sqllite manager 3.5 double clic on the table that you need to modify

michele
  • 1