I want to delete or add column in sqlite database
I am using following query to delete column.
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
But it gives error
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
This question is related to
sqlite
You can also now use DB browser for SQLite to manipulate columns
I rewrote the @Udinic answer so that the code generates table creation query automatically. It also doesn't need ConnectionSource. It also has to do this inside a transaction.
public static String getOneTableDbSchema(SQLiteDatabase db, String tableName) {
Cursor c = db.rawQuery(
"SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '" + tableName + "'", null);
String result = null;
if (c.moveToFirst()) {
result = c.getString(c.getColumnIndex("sql"));
}
c.close();
return result;
}
public List<String> getTableColumns(SQLiteDatabase db, String tableName) {
ArrayList<String> columns = new ArrayList<>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = db.rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}
private void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) {
db.beginTransaction();
try {
List<String> columnNamesWithoutRemovedOnes = getTableColumns(db, tableName);
// Remove the columns we don't want anymore from the table's list of columns
columnNamesWithoutRemovedOnes.removeAll(Arrays.asList(columnsToRemove));
String newColumnNamesSeparated = TextUtils.join(" , ", columnNamesWithoutRemovedOnes);
String sql = getOneTableDbSchema(db, tableName);
// Extract the SQL query that contains only columns
String oldColumnsSql = sql.substring(sql.indexOf("(")+1, sql.lastIndexOf(")"));
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
db.execSQL("CREATE TABLE `" + tableName + "` (" + getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove)+ ");");
db.execSQL("INSERT INTO " + tableName + "(" + newColumnNamesSeparated + ") SELECT " + newColumnNamesSeparated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
db.setTransactionSuccessful();
} catch {
//Error in between database transaction
} finally {
db.endTransaction();
}
}
My solution, only need to call this method.
public static void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) throws java.sql.SQLException {
List<String> updatedTableColumns = getTableColumns(db, tableName);
updatedTableColumns.removeAll(Arrays.asList(columnsToRemove));
String columnsSeperated = TextUtils.join(",", updatedTableColumns);
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
db.execSQL("CREATE TABLE " + tableName + " (" + columnsSeperated + ");");
db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
+ columnsSeperated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
}
And auxiliary method to get the columns:
public static List<String> getTableColumns(SQLiteDatabase db, String tableName) {
ArrayList<String> columns = new ArrayList<>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = db.rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}
This answer to a different question is oriented toward modifying a column, but I believe a portion of the answer could also yield a useful approach if you have lots of columns and don't want to retype most of them by hand for your INSERT statement:
https://stackoverflow.com/a/10385666
You could dump your database as described in the link above, then grab the "create table" statement and an "insert" template from that dump, then follow the instructions in the SQLite FAQ entry "How do I add or delete columns from an existing table in SQLite." (FAQ is linked elsewhere on this page.)
As SQLite has limited support to ALTER TABLE so you can only ADD column at end of the table OR CHANGE TABLE_NAME in SQLite.
Here is the Best Answer of HOW TO DELETE COLUMN FROM SQLITE?
We cannot drop a specific column in SQLite 3. See the FAQ.
You can use the SQlite Administrator for changing the column names. Right Click on Table name and select Edit Table.Here you will find the table structure and you can easily rename it.
As others have pointed out
It is not possible to rename a column, remove a column, or add or remove constraints from a table.
source : http://www.sqlite.org/lang_altertable.html
While you can always create a new table and then drop the older one. I will try to explain this workaround with an example.
sqlite> .schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
height INTEGER
);
sqlite> select * from person ;
id first_name last_name age height
---------- ---------- ---------- ---------- ----------
0 john doe 20 170
1 foo bar 25 171
Now you want to remove the column height from this table.
Create another table called new_person
sqlite> CREATE TABLE new_person(
...> id INTEGER PRIMARY KEY,
...> first_name TEXT,
...> last_name TEXT,
...> age INTEGER
...> ) ;
sqlite>
Now copy the data from the old table
sqlite> INSERT INTO new_person
...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id first_name last_name age
---------- ---------- ---------- ----------
0 john doe 20
1 foo bar 25
sqlite>
Now Drop the person table and rename new_person to person
sqlite> DROP TABLE IF EXISTS person ;
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>
So now if you do a .schema, you will see
sqlite>.schema
CREATE TABLE "person"(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER
);
you can use Sqlitebrowser. In the browser mode, for the respective database and the table, under the tab -database structure,following the option Modify Table, respective column could be removed.
DB Browser for SQLite allows you to add or drop columns.
In the main view, tab Database Structure, click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.
I've wrote a Java implementation based on the Sqlite's recommended way to do this:
private void dropColumn(SQLiteDatabase db,
ConnectionSource connectionSource,
String createTableCmd,
String tableName,
String[] colsToRemove) throws java.sql.SQLException {
List<String> updatedTableColumns = getTableColumns(tableName);
// Remove the columns we don't want anymore from the table's list of columns
updatedTableColumns.removeAll(Arrays.asList(colsToRemove));
String columnsSeperated = TextUtils.join(",", updatedTableColumns);
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
// Creating the table on its new format (no redundant columns)
db.execSQL(createTableCmd);
// Populating the table with the data
db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
+ columnsSeperated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
}
To get the table's column, I used the "PRAGMA table_info":
public List<String> getTableColumns(String tableName) {
ArrayList<String> columns = new ArrayList<String>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = getDB().rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}
I actually wrote about it on my blog, you can see more explanations there:
http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/
http://www.sqlite.org/lang_altertable.html
As you can see in the diagram, only ADD COLUMN is supported. There is a (kinda heavy) workaround, though: http://www.sqlite.org/faq.html#q11
As others have pointed out, sqlite's ALTER TABLE statement does not support DROP COLUMN, and the standard recipe to do this does not preserve constraints & indices.
Here's some python code to do this generically, while maintaining all the key constraints and indices.
Please back-up your database before using! This function relies on doctoring the original CREATE TABLE statement and is potentially a bit unsafe - for instance it will do the wrong thing if an identifier contains an embedded comma or parenthesis.
If anyone would care to contribute a better way to parse the SQL, that would be great!
UPDATE I found a better way to parse using the open-source sqlparse package. If there is any interest I will post it here, just leave a comment asking for it ...
import re
import random
def DROP_COLUMN(db, table, column):
columns = [ c[1] for c in db.execute("PRAGMA table_info(%s)" % table) ]
columns = [ c for c in columns if c != column ]
sql = db.execute("SELECT sql from sqlite_master where name = '%s'"
% table).fetchone()[0]
sql = format(sql)
lines = sql.splitlines()
findcol = r'\b%s\b' % column
keeplines = [ line for line in lines if not re.search(findcol, line) ]
create = '\n'.join(keeplines)
create = re.sub(r',(\s*\))', r'\1', create)
temp = 'tmp%d' % random.randint(1e8, 1e9)
db.execute("ALTER TABLE %(old)s RENAME TO %(new)s" % {
'old': table, 'new': temp })
db.execute(create)
db.execute("""
INSERT INTO %(new)s ( %(columns)s )
SELECT %(columns)s FROM %(old)s
""" % {
'old': temp,
'new': table,
'columns': ', '.join(columns)
})
db.execute("DROP TABLE %s" % temp)
def format(sql):
sql = sql.replace(",", ",\n")
sql = sql.replace("(", "(\n")
sql = sql.replace(")", "\n)")
return sql
I guess what you are wanting to do is database migration. 'Drop'ping a column does not exist in SQLite. But you can however, add an extra column by using the ALTER table query.
I have improved user2638929 answer and now it can preserves column type, primary key, default value etc.
public static void dropColumns(SQLiteDatabase database, String tableName, Collection<String> columnsToRemove){
List<String> columnNames = new ArrayList<>();
List<String> columnNamesWithType = new ArrayList<>();
List<String> primaryKeys = new ArrayList<>();
String query = "pragma table_info(" + tableName + ");";
Cursor cursor = database.rawQuery(query,null);
while (cursor.moveToNext()){
String columnName = cursor.getString(cursor.getColumnIndex("name"));
if (columnsToRemove.contains(columnName)){
continue;
}
String columnType = cursor.getString(cursor.getColumnIndex("type"));
boolean isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1;
boolean isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1;
columnNames.add(columnName);
String tmp = "`" + columnName + "` " + columnType + " ";
if (isNotNull){
tmp += " NOT NULL ";
}
int defaultValueType = cursor.getType(cursor.getColumnIndex("dflt_value"));
if (defaultValueType == Cursor.FIELD_TYPE_STRING){
tmp += " DEFAULT " + "\"" + cursor.getString(cursor.getColumnIndex("dflt_value")) + "\" ";
}else if(defaultValueType == Cursor.FIELD_TYPE_INTEGER){
tmp += " DEFAULT " + cursor.getInt(cursor.getColumnIndex("dflt_value")) + " ";
}else if (defaultValueType == Cursor.FIELD_TYPE_FLOAT){
tmp += " DEFAULT " + cursor.getFloat(cursor.getColumnIndex("dflt_value")) + " ";
}
columnNamesWithType.add(tmp);
if (isPk){
primaryKeys.add("`" + columnName + "`");
}
}
cursor.close();
String columnNamesSeparated = TextUtils.join(", ", columnNames);
if (primaryKeys.size() > 0){
columnNamesWithType.add("PRIMARY KEY("+ TextUtils.join(", ", primaryKeys) +")");
}
String columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType);
database.beginTransaction();
try {
database.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
database.execSQL("CREATE TABLE " + tableName + " (" + columnNamesWithTypeSeparated + ");");
database.execSQL("INSERT INTO " + tableName + " (" + columnNamesSeparated + ") SELECT "
+ columnNamesSeparated + " FROM " + tableName + "_old;");
database.execSQL("DROP TABLE " + tableName + "_old;");
database.setTransactionSuccessful();
}finally {
database.endTransaction();
}
}
PS. I used here android.arch.persistence.db.SupportSQLiteDatabase, but you can easyly modify it for use android.database.sqlite.SQLiteDatabase
Kotlin solution, based on here , but also ensures the temporary table doesn't already exist:
object DbUtil {
@JvmStatic
fun dropColumns(database: SQLiteDatabase, tableName: String, columnsToRemove: Collection<String>) {
val columnNames: MutableList<String> = ArrayList()
val columnNamesWithType: MutableList<String> = ArrayList()
val primaryKeys: MutableList<String> = ArrayList()
val query = "pragma table_info($tableName);"
val cursor = database.rawQuery(query, null)
while (cursor.moveToNext()) {
val columnName = cursor.getString(cursor.getColumnIndex("name"))
if (columnsToRemove.contains(columnName)) {
continue
}
val columnType = cursor.getString(cursor.getColumnIndex("type"))
val isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1
val isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1
columnNames.add(columnName)
var tmp = "`$columnName` $columnType "
if (isNotNull) {
tmp += " NOT NULL "
}
when (cursor.getType(cursor.getColumnIndex("dflt_value"))) {
Cursor.FIELD_TYPE_STRING -> {
tmp += " DEFAULT \"${cursor.getString(cursor.getColumnIndex("dflt_value"))}\" "
}
Cursor.FIELD_TYPE_INTEGER -> {
tmp += " DEFAULT ${cursor.getInt(cursor.getColumnIndex("dflt_value"))} "
}
Cursor.FIELD_TYPE_FLOAT -> {
tmp += " DEFAULT ${cursor.getFloat(cursor.getColumnIndex("dflt_value"))} "
}
}
columnNamesWithType.add(tmp)
if (isPk) {
primaryKeys.add("`$columnName`")
}
}
cursor.close()
val columnNamesSeparated = TextUtils.join(", ", columnNames)
if (primaryKeys.size > 0) {
columnNamesWithType.add("PRIMARY KEY(${TextUtils.join(", ", primaryKeys)})")
}
val columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType)
database.beginTransaction()
try {
var newTempTableName: String
var counter = 0
while (true) {
newTempTableName = "${tableName}_old_$counter"
if (!isTableExists(database, newTempTableName))
break
++counter
}
database.execSQL("ALTER TABLE $tableName RENAME TO $newTempTableName;")
database.execSQL("CREATE TABLE $tableName ($columnNamesWithTypeSeparated);")
database.execSQL("INSERT INTO $tableName ($columnNamesSeparated) SELECT $columnNamesSeparated FROM $newTempTableName;")
database.execSQL("DROP TABLE ${newTempTableName};")
database.setTransactionSuccessful()
} finally {
database.endTransaction()
}
}
@JvmStatic
fun isTableExists(database: SQLiteDatabase, tableName: String): Boolean {
database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '$tableName'", null)?.use {
return it.count > 0
} ?: return false
}
}
As an alternative:
If you have a table with schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
height INTEGER
);
you can use a CREATE TABLE...AS statement like CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person;, i.e. leave out the columns you don't want. Then drop the original person table and rename the new one.
Note this method produces a table has no PRIMARY KEY and no constraints. To preserve those, utilize the methods others described to create a new table, or use a temporary table as an intermediate.
public void DeleteColFromTable(String DbName, String TableName, String ColName){
SQLiteDatabase db = openOrCreateDatabase(""+DbName+"", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS "+TableName+"(1x00dff);");
Cursor c = db.rawQuery("PRAGMA table_info("+TableName+")", null);
if (c.getCount() == 0) {
} else {
String columns1 = "";
String columns2 = "";
while (c.moveToNext()) {
if (c.getString(1).equals(ColName)) {
} else {
columns1 = columns1 + ", " + c.getString(1) + " " + c.getString(2);
columns2 = columns2 + ", " + c.getString(1);
}
if (c.isLast()) {
db.execSQL("CREATE TABLE IF NOT EXISTS DataBackup (" + columns1 + ");");
db.execSQL("INSERT INTO DataBackup SELECT " + columns2 + " FROM "+TableName+";");
db.execSQL("DROP TABLE "+TableName+"");
db.execSQL("ALTER TABLE DataBackup RENAME TO "+TableName+";");
}
}
}
}
and just call a method
DeleteColFromTable("Database name","Table name","Col name which want to delete");
Implementation in Python based on information at http://www.sqlite.org/faq.html#q11.
import sqlite3 as db
import random
import string
QUERY_TEMPLATE_GET_COLUMNS = "PRAGMA table_info(@table_name)"
QUERY_TEMPLATE_DROP_COLUMN = """
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE @tmp_table(@columns_to_keep);
INSERT INTO @tmp_table SELECT @columns_to_keep FROM @table_name;
DROP TABLE @table_name;
CREATE TABLE @table_name(@columns_to_keep);
INSERT INTO @table_name SELECT @columns_to_keep FROM @tmp_table;
DROP TABLE @tmp_table;
COMMIT;
"""
def drop_column(db_file, table_name, column_name):
con = db.connect(db_file)
QUERY_GET_COLUMNS = QUERY_TEMPLATE_GET_COLUMNS.replace("@table_name", table_name)
query_res = con.execute(QUERY_GET_COLUMNS).fetchall()
columns_list_to_keep = [i[1] for i in query_res if i[1] != column_name]
columns_to_keep = ",".join(columns_list_to_keep)
tmp_table = "tmp_%s" % "".join(random.sample(string.ascii_lowercase, 10))
QUERY_DROP_COLUMN = QUERY_TEMPLATE_DROP_COLUMN.replace("@table_name", table_name)\
.replace("@tmp_table", tmp_table).replace("@columns_to_keep", columns_to_keep)
con.executescript(QUERY_DROP_COLUMN)
con.close()
drop_column(DB_FILE, TABLE_NAME, COLUMN_NAME)
This script first makes random temporary table and inserts data of only necessary columns except the one that will will be dropped. Then restores the original table based on the temporary table and drops the temporary table.
SQLite 3.35.0 will introduce support for ALTER TABLE DROP COLUMN.
The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and also rewrites the entire table to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint.
The following syntax will be valid:
ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
ALTER TABLE <TABLENAME> DROP <COLUMNNAME>;
Source: Stackoverflow.com