Hacker News Reader

Comment by breeze1990 | Hacker News Reader

breeze1990

It's newly added and even so, it has limited support if you come from e.g. MySQL world.

In what sense does DROP COLUMN have limited support compared to mysql?

Aside from being transactional, which is the opposite of limited.

"SQLite stores the schema as plain text in the sqlite_schema table. The DROP COLUMN command (and all of the other variations of ALTER TABLE as well) modify that text and then attempt to reparse the entire schema. The command is only successful if the schema is still valid after the text has been modified. In the case of the DROP COLUMN command, the only text modified is that the column definition is removed from the CREATE TABLE statement. The DROP COLUMN command will fail if there are any traces of the column in other parts of the schema that will prevent the schema from parsing after the CREATE TABLE statement has been modified."

You can't drop a column which is still in use, I fail to see the issue with that.

I might see lamenting the lack of CASCADE but I've come to be less than fond of that in postgres, I don't consider that a feature.

And since mysql does not have DROP COLUMN ... CASCADE does it just... destroy any dependent when you try to drop a column being depended on? That sounds like the opposite of a feature.

No, that's not how it works. If the column is used in a foreign key constraint, MySQL will prevent you from dropping the column. In that case, the ALTER statement will immediately fail with an error and take no action.

Otherwise, assuming there's no FK and it proceeds:

If the dropped column is used in any indexes, the index definitions are adjusted to no longer be keyed on that column. And if there's a single-column index on that dropped column, the index is also dropped. It's pretty much what you would always want to happen, without forcing you to needlessly indicate how the indexes need to be adjusted.

It doesn't automatically adjust any views, triggers, procs, funcs, etc that reference the column though.

Reply on news.ycombinator.com