SQLite Forum

DROP COLUMN feature with indexes
Please, no new "DROP TABLE IF EXISTS" affair! 😉

What I mean is that DROP COLUMN should not fail in such a case. This might be nice when working in the SQLite-CLI, but it is a no-go for automatic routines. It makes DROP COLUMN worthless if one has to know about the backgrounds to get it to work.

A common usecase of an application database is upgrading the database schema. Due to the restricted ALTER TABLE command, this is currently a very complex and error-prone procedure. Imho, it should get *easier* to handle with DROP COLUMN, and not only shift the complexity.

Example: An application upgrades its database schema and knows that column X has to be dropped because it has been replaced by something else. The application has never used an index on column X, so the upgrade procedure just says "DROP COLUMN X". But some user might have used X quite often and added an index for better performance. Bang -- the application's upgrade procedure fails. Solution: query all indexes first, remember them, drop what is needed, drop column, maybe recreated some indexes.... does one have to do the same with views as well? This is no improvement. It just keeps the complexity of the current ALTER TABLE procedure.