Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite drop column routine

Tags:

sqlite

I need to write a DROP COLUMN routine for manipulating SQLite databases.

It would be called something like this:

dropColumn("SomeTable", "SomeColumn");

The SQLite FAQ says that to drop a column, you have to create a temporary table containing just the columns you want, and then copy the data across to it, and then rename it.

It shouldn't be too hard to encapsulate that into a routine. But it looks like it would be kind of annoying to write it.

Surely someone out there has written such a routine already. If so, can I steal it please? :)

like image 252
dan-gph Avatar asked Oct 22 '10 08:10

dan-gph


People also ask

How do I drop a column in SQLite?

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.

Does SQLite support drop column?

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.

How do I delete a specific column in SQL?

Right-click the column you want to delete and choose Delete Column from the shortcut menu. If the column participates in a relationship (FOREIGN KEY or PRIMARY KEY), a message prompts you to confirm the deletion of the selected columns and their relationships. Choose Yes.

How can you add and remove any column of a table explain with example?

First, we need to specify the table name. Next, after the CHANGE COLUMN clause, we have to specify the old column name and new column name along with its definition. We must have to specify the column definition even it will not change. Finally, we need to specify the FIRST or AFTER keyword.


1 Answers

Here's some pseudo code for you:

columnNameList = ""
newTableStr = "CREATE TABLE tempMyTable ("
execute statement: "PRAGMA table_info('MyTable')"
While looping through RecordSet
  If RecordSet.name != tableRowToDeleteName
    If columnNameList != "" Then columnNameList += ","
    columnNameList += RecordSet.name

    newTableStr += RecordSet.name + " " + RecordSet.type
    If RecordSet.notnull Then
      newTableStr += " NOT NULL"
    End If
    If RecordSet.dflt_value != "" Then
      newTableStr += " DEFAULT(" + RecordSet.dflt_value + ")"
    End If
    If Not Last Record in RecordSet
      newTableStr += ","
    End If
  End If
End Loop
newTableStr += ");"

execute statement: newTableStr
execute statement: "INSERT INTO tempMyTable (" + columnNameList + ")" + 
                   "SELECT " + columnNameList + " FROM MyTable;"

Delete table: MyTable
Rename Table: tempMyTable to MyTable
like image 61
Sparafusile Avatar answered Sep 20 '22 17:09

Sparafusile