Renaming SQLite Tables/Columns/Rows after indices have been created

Question

If I rename SQLite Tables/Columns/Rows after indices have been created, will the old indices still be functional?

Thanks!

Richard Inglis · Accepted Answer · 2011-08-01T08:08:26.620

6

If you're using ALTER TABLE with RENAME TO to rename a table, then as described on this page (from the sqlite docs) the indices will still work:

The ALTER TABLE command in SQLite allows the user to rename a table [...] If the table being renamed has triggers or indices, then these remain attached to the table after it has been renamed.

But note there's no renaming of columns allowed. This is one of the SQL features not implemented by sqlite:

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Rows don't have names (except in the sense of having a PK) so there's not really a way of renaming them.

edited Aug 01 '11 at 08:08

answered Jul 31 '11 at 20:02

Richard Inglis

5,888
2
33
37

Is there a way to copy over the information from one column to another, and then delete the first column? – joshim5 Jul 31 '11 at 20:17
See this [other SO question](http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table) on renaming columns – Richard Inglis Jul 31 '11 at 20:22
I was actually just looking through that :). The first answer looks great, but how should I deal with the indices? – joshim5 Jul 31 '11 at 20:25
I think you need to recreate them manually. The [sql FAQ](http://www.sqlite.org/faq.html#q7) has instructions on how you can get a list of the existing indices before you start the process. – Richard Inglis Jul 31 '11 at 20:35

score 0 · Answer 2 · edited May 23 '17 at 12:07

Yes, the old indices will still be functional.

Be aware, that sqlite doesn't care about the names for the indexes. Initially when an index is created usually they are named after the table and field, so when you rename the table, the indexes will still have the name of the old table in it. This can cause problems, when you for example:

dump the table

rename the old table:

sqlite3 "$DB" "PRAGMA busy_timeout=20000; ALTER TABLE '$TABLE' RENAME TO '$TABLE"_backup"'"

reimport the dumped table

This will cause an error, that the indexes already exist.

Solution: Rename the indexes too, or delete them in the renamed table before you reimport the original (see this answer).

score 0 · Answer 3 · answered Aug 01 '11 at 08:14

I highly recommend using Rails ActiveRecord migrations to maintain your database. This can be done outside of Rails. So you app doesn't need to be a Rails app to use rake tasks

See here for an excellent blog on how to do this http://exposinggotchas.blogspot.com/2011/02/activerecord-migrations-without-rails.html

Renaming SQLite Tables/Columns/Rows after indices have been created

3 Answers3