SQLite Forum

DROP COLUMN feature with indexes
> Imho, DROP COLUMN should not fail because of such a banality as it makes it impossible to due automatic database manipulation (see my post above).

My personal feeling is that this isn't a strong argument. If a system is making an 'automatic' manipulation, it should know enough about the database schema to be able to 'automatically' do the steps needed to fix the issue before trying to drop the column. Otherwise, it seems a bit like giving a five-year-old a loaded handgun and telling them to 'play safe'. 

For cases like this, I like the line from the Zen of Python:

> In the face of ambiguity, refuse the temptation to guess.

It MIGHT be safe to just drop the column out of the index, or it might be that the whole index is now worthless (do we spend the cycles to see if it is now a redundant index, or try to guess if it will ever be used?). Removing the column in cases like a UNIQUE index may well create a constraint violation, in that case, do we switch from removing the column to dropping the index, or does that mean we should fail the DROP COLUMN command?

Particularly when adding a new feature, it is easier to start in the most restricted case, and we can later make it do more. Due to backward compatibility constraints, once you can't as easily take back an automatically done action as adding it later. (Look at things like the fact that primary keys are allowed to be NULL, which has to remain because of backward compatibility).