iOS Question iSQLite change integer primary key.

[MitchBu]

I have a table where I deleted several records. As a result, the primary key has holes. For instance 1,2,3, then 5,6,7 and so on.

I want to reorder the primary key, so now it goes as 1 2 3 4 5 6.

Here is the structure of the table:
ID INTEGER PRIMARY KEY, Date TEXT, Number TEXT, Payee TEXT, Memo TEXT, CheckMark TEXT, Amount TEXT, Balance TEXT

Is there any way to change the ID integer primary key so I can reorder it ?

TIA

 

[aeric]

You can update the ID but the table will have new ID 8 instead of 7. You can drop the ID column and then add it back so it will starts counting from 1.

 

[MitchBu]

You can update the ID but the table will have new ID 8 instead of 7. You can drop the ID column and then add it back so it will starts counting from 1.

Great. How do I drop the ID column ? And add it back ?

 

[MitchBu]

In the meantime, I have found a way to change the ID, with this:

IDList.Set(CurrentIndex, 123) '123 is the new value

 

[aeric]

I just checked SQLite related websites. It is not possible to drop the column and add primary key column. You will need to create a new temporary table, copy the data from the old table, drop the table then rename the new temporary table to the old table name. If you are not doing it by SQL syntax, using software like DBBrowser may simplifies the job.

 

[MitchBu]

Thank you Aeric.

I just found out, though, that ID actually works by the right order, so I don't need to concern myself if I delete records.

 

[aeric]

If the table stores transactions, you may not want to delete any records. Just add a column for void and set it as true if the sales is cancelled then filter the records for void equals false for actual sales.