Xojo Documentation Xojo Documentation

SQLite 3.25 and later added the ability for the ALTER table command to rename a column. This version of SQLite is available starting with Xojo 2018r4. Using ALTER TABLE makes renaming a column very easy.

The syntax is as you might expect:

ALTER TABLE MyTable RENAME COLUMN OldColumn TO NewColumn;

In Xojo code you send this command to the database using the SQLExecute method. Here’s an example that changes a column name:

Dim sql As String = "ALTER TABLE Team RENAME COLUMN Coach To HeadCoach;"
DB.SQLExecute(sql)

If you need to delete a column you will want to use the technique described below.

SQLite 3.24.x and earlier

In earlier versions of SQLite, the ALTER TABLE command cannot be used to rename an existing column on a table. The workaround is to create a new table with the columns named the way you want and then copy the data from the old table to the new table.

For example, suppose you created a table with this SQL:

CREATE TABLE team(Name TEXT, Coach TEXT, City TEXT);

You later realize that the City column ought to instead be called Location. These are the steps you can do:

1. Rename the original table with this SQL:

   ALTER TABLE team RENAME TO team_orig;

2. Create the replacement table with the original name and corrected column name with this SQL:

   CREATE TABLE team(Name TEXT, Coach TEXT, Location TEXT);

3. Copy the data from the original table to the new table with this SQL:

   INSERT INTO team(Name, Coach, Location) SELECT Name, Coach, City FROM team_orig;

4. Drop the original table with this SQL:

   DROP TABLE team_orig;

With these four steps you can manually change any SQLite table. Keep in mind that you will also need to recreate any indexes, viewers or triggers on the new table as well.

You can also use the above steps to remove a column from a table as SQLite has no ability to do that. In this case you would create the new table without the column you want to delete and then don't copy that column's data over to it.

Typically you will run the above commands using your favorite SQL tool. But you can also send the commands from Xojo by using the Database.SQLExecute method.

There is also a way to combine steps 2 and 3 into a single SQL command that creates the table and populates its data in a single step. The SQL looks like this:

CREATE TABLE team AS SELECT Name, Coach, City AS Location FROM team_orig;

See Also

SQLiteDatabase, iOSSQLiteDatabase classes; UserGuide:Framework topic