#Alter table sqlite drop column full#

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?). In the face of ambiguity, refuse the temptation to guess. Otherwise, it seems a bit like giving a five-year-old a loaded handgun and telling them to 'play safe'.įor cases like this, I like the line from the Zen of Python: 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. My personal feeling is that this isn't a strong argument. 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). The moment however there is any index using that column in addition to any other, and I did not explicitly 'fix' that index before trying to drop that column, please error out - Loudly and clearly. This also means that if I drop the index explicitly before the column (the way I like to do it), everything still works exactly. If an entire index exists based on only the column being dropped, I'm happy for that Index to be dropped because the database goes from consistent state to consistent state, and if you do not need the column anymore, you must not need that index anymore. Show PostGres doing it, and you may have a more valid point.

Lastly - MariaDB/MySQL, great DB as it is, should not be held up as the defacto standard for DB operations, ever. I would very much like to know this (by error message) before I go ahead and roll it out to actual production environments 1. I for one would hate if SQLite starts doing magic background things when I try to alter a table in a way that doesn't stroke with the current rest of the schema.

#Alter table sqlite drop column full#

There is no substitute for proper and full data statements. Impossible to do it in a lazy way? Maybe, and if so, Good. 'Impossible'? - That's just demonstrably false. 'Banality'? - Saying 'Explicitly' what you want to happen, for EVERY piece of the puzzle, is a base tenet of accurate programming. The use of hyperbole won't make your point any stronger.ĭROP COLUMN should not fail because of such a banality as it makes it impossible to due automatic database manipulation Table names being dropped that are used in the body of a view or trigger that is not ON that table should act to prohibit the table from being dropped unless the user has indicated that they wish to allow an inconsistent schema result (pragma legacy_alter_table).Ĭolumn names being dropped that are referenced in a view or trigger body (or in an index) should also cause the drop of the column name to fail with an error rather than leaving the schema in an inconsistent state UNLESS one has specifically enabled the option for allowing an inconsistent schema (pragma legacy_alter_table). If you drop a table then all triggers which apply to that table (as in specified in the ON clause) should be dropped because they are entirely no longer applicable. To me that is like asking the user to manually delete triggers before dropping a table. The use of a DDL command should not result in an invalid schema unless you have specified that this is the result you intend. But then neither should the DROP COLUMN command fail before the surrounding transaction commits.ĭo you have a rationale for why you prefer fail to drop? In that case an immediate dropping of related views and triggers would not be so useful.

It would possibly be annoying to have to recreate all views and triggers here. Similarly, perhaps one could imagine wanting to change a column type, leaving the rest of the schema alone: BEGIN Ideally that would only be allowed inside a transaction and rejected at COMMIT time unless resolved by additional DDL statements. There is however at least one situation today where DDL does result in an inconsistent schema: the first table of a circular foreign key reference. Why make them jump through the extra hoops? To me that is like asking the user to manually delete triggers before dropping a table. Do you have a rationale for why you prefer fail to drop? If the user intends to drop a column and SQLite says you can't because of x,y and z, then the user has to manually do that work anyway.