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

If I rename SQLite Tables/Colu开发者_运维问答mns/Rows after indices have been created, will the old indices still be functional?

Thanks!

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.

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

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).