4 ms·

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

by breeze1990 3y ago

masklinn 3y ago
In what sense does DROP COLUMN have limited support compared to mysql? Aside from being transactional, which is the opposite of limited.
- dkjaudyeqooe 3y ago
  "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."
  - masklinn 3y ago
    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.
    - evanelias 3y ago
      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.

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.

dkjaudyeqooe 3y ago
"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."
- masklinn 3y ago
  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.
  - evanelias 3y ago
    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.