Is it possible to delete a Column from a Table created with SQLite ?
(ALTER TABLE ?)
No, you have to copy the table to a temporary one, delete the original, re-create it without the column you wish to delete, and pull the data back in from the temporary.
table t1 has 3 columns a,b,and c. you want to remove column c.
BEGIN TRANSACTION;
CREATE TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;
COMMIT;
from http://stackoverflow.com/questions/5938048/delete-column-from-sqlite-table
for renaming only columns, you can edit the sqlmaster table.
Interesting. I did not know that. Apparently you also have to set a pragma first and the SQLite docs warn you could easily corrupt your DB.
you will corrupt the db if you change the order of the columns, or remove or insert columns
(althought I did not try it just suppose it may fail)
you can also change type of data from text to numbers it works by editing the sqlmaster.
you can also with this change the primary key (you have to make the new index yourself)
also you must close and reopen the db after editing sqlmaster for the changes to take effect.
Where I have needed this I have created the new column (ALTER TABLE ADD columnName TEXT), copied the data from the old column (UPDATE tableName SET newColumn = oldColumn), emptied the values from the old column (DELETE FROM oldColumn), then run a vacuum (VACUUM tableName).
The old column is still there, but you can ignore it.
Thank you all for your answers.
At think time (change design time), the simple solution was to add the needed Columns, then I noticed I forgot to remove a useless Column (either SQLite or Xojo remember this to me with errors at run time and now I am here.
I build an utility that allow me to load all defined table including sql_master and report their contents.
I just fired it and I can see my three added Columns at the end of the previous columns. I never think I can modify the sql_master contents !
In my code, I use the Column Names to access their contents, so from the data base point of view, the order of appearance of the Columns is not a problem for me.
Since now, because it tooks me time to add Columns to the Table, I do not wanted to rename the old table create a new one and populate it with the old contents / delete the “old Tables”.
If it is possible to rename the now useless Column name (in sql_master), I can change my current code (only add two new Columns and give a new name to the useless COlumn fo fit my needs).
The question is How do I edit the sql_master Table ? RecordSet, search the Record that holds the Column to change its name, do it just like any RecordSet,
The more I think at this problem to be solved, the more I think the solution is to create a brand new Table with the Columns I need, then drop the previous one (or better: create a brand new file). This seems to me the simple and fast solution.
I only have less than 2,000 Records / 11 Columns (including the Unique ID). These number are not meant to see changes: these 2,000 records are people data and the number of people they can deal with cannot grow without hiring personal / having the target population must not grow
Tim is the winner. Thank you Tim.
I would suggest to not delete it and simply ignore it.
On the next time you copy stuff to a new database you can skip the obsolete fields.
[quote=302138:@Christian Schmitz]I would suggest to not delete it and simply ignore it.
On the next time you copy stuff to a new database you can skip the obsolete fields.[/quote]
This should not be a considered option… especially if the database might be used by another application.
It should always be an imperative to keep the structure of you database as optimized as possible, for speed, footprint, and maintainabilty.
ESPECIALLY if the “ignored” column is marked as “NOT NULL”…
For personal/hobby databases that are relatively small… not a huge problem… but its a bad habit to get into
Hi Dave:
You are right. There is another thing I try to do that needs an explanation. When I have to modify someones else work (not only in our businesss) and I think how can someone works that bad, it do not gaves me the idea (etc.) to work like that. I try to do the things the way they are supposed to be done (so no one will tell that about me). I hope this is clear enough.
Of course, I do not want to make a new Data Base file and copy the older data into the new one. But if this is the real way to do it properly, I will do that. Fortunately, I have a bit of time (until January 1st) do achieve that.
Thank you all.
I enarly forgot: as far as I know, and with SQLite, NOT NULL will still allow you to write nothing in these Columns. I once even was able to write text in an INTEGER Column. I noticed that non ASCII characters were displaying wrong and that allows me to spot the error. Making errors are inside the computing experience.
Feedback:
I implemented the new methodology (design): I create a brand new sqlite data base file, set the needed Tables, write the service Records, then read the old format db file sequencially / add a record into the new db file.
Once done, I rename the old db format file (I prefix it with “OLD-”), rename the new db file. The operation last around 3 seconds.
Thank you all for your help.
if it seems usefull for you, a 20 investment will let you edit sqlite columns
https://itunes.apple.com/fr/app/sqlpro-for-sqlite-database/id586001240?mt=12
don’t buy the postgres version it is all but usable. the sqlite is complete.
No, thank you Jean-Yves. This is a Pro Bono project. (and it will run on a single PC with WIndows XP, go figure. That PC is not conencted to anything: no printer, no internet, nothing).
This will let you edit or delete columns, and it’s free: http://sqlitestudio.pl/
I use it all the time.
Thank you Ralph.