drop - ¿Cómo eliminar o agregar columna en SQLITE?

drop column sqlite

Question

Quiero eliminar o agregar una columna en la base de datos sqlite

Estoy utilizando la siguiente consulta para eliminar la columna.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

Pero da error

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

Answer 1

Ahora también puede usar el navegador de base de datos para SQLite para manipular columnas

Answer 2

Como SQLite tiene soporte limitado para ALTER TABLE, solo puede AGREGAR la columna al final de la tabla O CAMBIAR TABLE_NAME en SQLite.

Aquí está la mejor respuesta de ¿CÓMO BORRAR LA COLUMNA DE SQLITE?

visita Eliminar columna de la tabla SQLite

Answer 3

Como alternativa:

Si tienes una tabla con esquema

CREATE TABLE person(
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  height INTEGER
);

puede usar una CREATE TABLE...AS como CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person; , es decir, deja fuera las columnas que no quieres. Luego suelte la tabla de person original y cambie el nombre de la nueva.

Tenga en cuenta que este método produce una tabla que no tiene CLAVE PRIMARIA ni restricciones. Para preservarlos, utilice los métodos que otros describen para crear una nueva tabla, o use una tabla temporal como intermedio.

Answer 4

Como han señalado otros

No es posible cambiar el nombre de una columna, eliminar una columna o agregar o eliminar restricciones de una tabla.

fuente: http://www.sqlite.org/lang_altertable.html

Mientras que siempre puedes crear una nueva tabla y luego quitar la anterior. Intentaré explicar esta solución con un ejemplo.

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       

Ahora desea eliminar la height columna de esta tabla.

Crear otra tabla llamada new_person

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 
sqlite> 

Ahora copia los datos de la tabla antigua

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>

Ahora suelte la tabla de person y cambie el nombre de new_person a person

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>

Así que ahora si haces un .schema , verás

sqlite>.schema
CREATE TABLE "person"(
 id INTEGER PRIMARY KEY, 
 first_name TEXT, 
 last_name TEXT, 
 age INTEGER 
);

Answer 5

Como han señalado otros, la declaración ALTER TABLE sqlite no es compatible con DROP COLUMN , y la receta estándar para hacerlo no conserva las restricciones y los índices.

Aquí hay algo de código de Python para hacer esto genéricamente, mientras se mantienen todas las restricciones e índices clave.

Por favor haga una copia de seguridad de su base de datos antes de usar! Esta función se basa en manipular la instrucción CREATE TABLE original y es potencialmente un poco insegura; por ejemplo, hará lo incorrecto si un identificador contiene una coma o paréntesis incrustados.

Si alguien quisiera contribuir con una mejor manera de analizar el SQL, ¡sería genial!

ACTUALIZACIÓN Encontré una mejor manera de analizar utilizando el paquete sqlparse código sqlparse . Si hay algún interés, lo publicaré aquí, solo deje un comentario solicitándolo ...

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

Answer 6

Ejemplo para agregar una columna: -

alter table student add column TOB time;

aquí el alumno es table_name y TOB es column_name para agregarse.

Está funcionando y probado.

Answer 7

Esta respuesta a una pregunta diferente está orientada a modificar una columna, pero creo que una parte de la respuesta también podría proporcionar un enfoque útil si tiene muchas columnas y no desea volver a escribir la mayoría de ellas a mano para su declaración INSERT:

https://.com/a/10385666

Puede volcar su base de datos como se describe en el enlace anterior, luego tomar la declaración "crear tabla" y una plantilla "insertar" de ese volcado, luego seguir las instrucciones en la entrada de preguntas frecuentes de SQLite "¿Cómo agrego o elimino columnas de un archivo existente? tabla en SQLite ". (Las preguntas frecuentes están enlazadas en otra parte de esta página).

Answer 8

He escrito una implementación de Java basada en la forma recomendada por Sqlite de hacer esto:

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;");
}

Para obtener la columna de la tabla, usé el "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;
}

En realidad escribí sobre eso en mi blog, puedes ver más explicaciones allí:

http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/

Answer 9

He mejorado la respuesta de user2638929 y ahora puedo conservar el tipo de columna, la clave principal, el valor predeterminado, etc.

private static void dropColumn(SupportSQLiteDatabase database, String tableName, List<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.query(query);
    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();
    }
}

PD. Utilicé aquí android.arch.persistence.db.SupportSQLiteDatabase , pero puede modificarlo fácilmente para usar android.database.sqlite.SQLiteDatabase

Answer 10

Implementación en Python basada en información en 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)

Este script primero crea una tabla temporal aleatoria e inserta datos de solo las columnas necesarias, excepto la que se eliminará. Luego restaura la tabla original basada en la tabla temporal y suelta la tabla temporal.

Answer 11

Mi solución, solo necesito llamar a este método.

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;");
}

Y método auxiliar para obtener las columnas:

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;
}

Answer 12

No podemos eliminar una columna específica en SQLite 3. Ver las FAQ .

Answer 13

Puede utilizar el Administrador de SQlite para cambiar los nombres de columna. Haga clic con el botón derecho en Nombre de la tabla y seleccione Editar tabla. Aquí encontrará la estructura de la tabla y podrá cambiarle el nombre fácilmente.

Answer 14

Puedes usar Sqlitebrowser. En el modo de navegador, para la base de datos respectiva y la tabla, debajo de la estructura de la pestaña-base de datos, luego de la opción Modificar tabla, se podría eliminar la columna correspondiente.

Answer 15

Reescribí la respuesta de @Udinic para que el código genere la consulta de creación de tablas automáticamente . Tampoco necesita ConnectionSource . También tiene que hacer esto dentro de una transacción .

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();
    }


}

Answer 16

http://www.sqlite.org/lang_altertable.html

Como puede ver en el diagrama, solo se admite ADD COLUMN. Sin embargo, hay una solución (bastante pesada): http://www.sqlite.org/faq.html#q11

Answer 17

ALTER TABLE SQLite

SQLite soporta un subconjunto limitado de ALTER TABLE. El comando ALTER TABLE en SQLite permite al usuario cambiar el nombre de una tabla o agregar una nueva columna a una tabla existente. No es posible cambiar el nombre de una columna, eliminar una columna o agregar o eliminar restricciones de una tabla.

Usted puede:

1. Crea una nueva tabla como la que estás intentando cambiar,
2. copiar todos los datos,
3. dejar caer la mesa vieja,
4. renombra el nuevo.

Answer 18

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+";");
            }
        }
    }
}

y solo llama a un método

DeleteColFromTable("Database name","Table name","Col name which want to delete");