CODEWITHSUNDEEP
sqlsqlitedatabase-design
Efhache
Efhache

Reputation: 167

SQLite3 : How to modify fileds'type without data loss

In my project I use a sqlite database, unfortunately my friend have make an error. A field is with no correct type. So for the moment when the data in the field 'localid' (declare as integer) is more than 2147483647, all entry in this field is set to the max 2147483647.

The alter table/alter column sql request do not works with sqlite because it supports a limited subset of ALTER TABLE : only rename and add a new column.

So how can I make a change without data loss? create a new database correctly, coppy all data into it and delete the old?

But maybe there is a better way ? Someone have an idea?

Upvotes: 4

Views: 259

Answers (1)

Mikko Maunu
Mikko Maunu

Reputation: 42084

Proceed like this:

  1. create temporary table that contains fields that form the primary key and localid (I assume this is nor PK).
  2. fill temporary table
  3. drop old column
  4. add new column
  5. fill new column by selecting from temporary table (+ possible conversion to new type).

Don't forget possible foreign keys if column is used as such and remember possibility to temporally relax constraint if it makes conversion smooth (likely not needed in your case).

Upvotes: 2

Related Questions