3

I've created which used a column which were not being used initially but now we are setting and getting values

I found the column name is not correct and what i want to change column Name but want to retain the data of existing database in device

Is there any query to check and rename sqlite Column Something like this

Alter Table MyTable RENAME COLUMN IF EXISTS MyColumn TO MyColumn1;

Existing database is at user end used by user's throw out INDIA please help me

Note:

1. I don't want to Threw Exception

2. Also not want to recreate table

I know both way will accomplish the task but that's not the best practice

Rather than recreating i'll live with poor name

I think there could be solution like as we can check table like this hope we could check column

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

OR

SELECT name FROM sqlite_master WHERE type='table' AND name = 'MyTable';
Community
  • 1
  • 1

2 Answers2

8

As far as I know there is no way to do what you ask. However you can use

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

to check whether the column exists or not. However since you just have to rename the column once I do not know what the issue is with the recreation of the table. Recreation does NOT mean data loss.

The procedure would be along the lines of:

  1. BEGIN TRANSACTION;
  2. ALTER TABLE table RENAME TO tmp_table;
  3. CREATE TABLE table (columnNames);
  4. INSERT INTO table(columnNames) SELECT columnNamesWrong FROM tmp_table;
  5. DROP TABLE tmp_table_name;
  6. COMMIT;

If this is to much fuss use a tool to do it.

About the best practice part, it is best practice to get your tables named properly. Since you usually build all your queries around the field names renaming columns means breaking those queries. I do not know what you are looking for but the sqlite manual states:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Note what is NOT possible.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ted
  • 4,791
  • 5
  • 38
  • 84
  • 1
    I know recreation is not dataloss –  Aug 08 '13 at 08:55
  • Why should i every time recreate the table I'll only do it once and will check either time –  Aug 08 '13 at 08:56
  • I am just coping the database if not exists –  Aug 08 '13 at 08:59
  • @FatalError: I can't follow you. I assume your `database` exists and all you want to do is to renam a `column` inside a `table`. So I am not sure where you are going at with the first and third comment. For the second comment: yes obviously you only run the described procedure once, after all, once you run the procedure the `4.` step will fail since you try to select a `column` that is not existing (you renamed it, so the old name is gone). – ted Aug 08 '13 at 11:26
  • @FatalError: Your question was tagged `android`, I assume you want to change the database format in an app update. I do not know much about android I suggest you look if there is a part were you can execute code durring the update (check with the first query if the table needs updating, if so proceed) or run these steps during every application start (once the db is updated it will just check with the querry `SELECT sql FROM sqlite_master WHERE ...`, that the db does not need updating). If I missundersotod you and column renaming is part of normal operation your db design needs rethinking. – ted Aug 08 '13 at 11:32
2

It is actually possible to rename columns in SQLite:

  1. Read the table definition by executing this query:

    SELECT sql FROM sqlite_master WHERE type='table' AND name='MyTable'

    This will give you the entire CREATE TABLE statement.

    You then have to check, in your code, whether this table definition contains the old or the new column name. If it already has the new name, stop. If not, replace the old name with the new name.

  2. Execute the following magic command to allow changing the table definition:

    PRAGMA writable_schema = on

  3. Update that record in the sqlite_master table with the new table definition:

    UPDATE sqlite_master SET sql = ? WHERE type='table' AND name='MyTable'

  4. Switch the dangerous setting back:

    PRAGMA writable_schema = off

Please note that this is a very dangerous mechanism; SQLite uses the text in the sql column to determine how the data is stored in the database file, so changing almost anything except the column name will definitely result in a corrupted database.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • yes i know sqlite is really a dangerous database when sqlite manager hang on some syntax error lol –  Aug 08 '13 at 09:46