# Dropping Columns Like It's Hot

DevFeed: [Dropping Columns Like It's Hot](<https://devfeed.tech/articles/dropping-columns-like-it-s-hot-25109.md>)

Original publisher: [Read original article](<http://michaelevans.org/blog/2020/02/10/dropping-columns-like-its-hot/>)

Author: Michael Evans

Published: 2020-02-11T01:03:03Z

Content type: article

Language: en

Sources: [Gadget Habit](<https://devfeed.tech/sources/gadget-habit.md>)

Topics: [SQLite](<https://devfeed.tech/topics/sqlite.md>), [Structured-data](<https://devfeed.tech/topics/structured-data.md>), [migration](<https://devfeed.tech/topics/migration.md>), [Code](<https://devfeed.tech/topics/code.md>)

Tags: [android](<https://devfeed.tech/tags/android.md>), [code](<https://devfeed.tech/tags/code.md>), [database](<https://devfeed.tech/tags/database.md>), [migration](<https://devfeed.tech/tags/migration.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sqlite](<https://devfeed.tech/tags/sqlite.md>)

## AI overview

This article explains why dropping a column in SQLite can fail when a migration library emulates the operation by renaming the table, creating a replacement table, copying the retained data, and deleting the old table. It highlights how SQLite versions 3.25.0 and 3.26.0 changed table-rename behavior for triggers and views, which can affect this migration process.

## Source excerpt

Recently, I was doing some code cleanup and noticed that there were some data in the database that was no longer needed. I think most developers clean up their codebase of deprecated patterns and unused code, but I personally have not done a good job of ensuring that the same cleanup happens for unused columns in my databases. Dropping tables that are no longer used is pretty easy (especially if you can just use something like Room's Migrations) but when trying to remove unused columns, I ran into an unexpected problem. I thought to myself, it's pretty easy to add or rename a column, why would dropping one be any harder? The existing database library I was using already had a convenient "drop column" method, so I simply called that and tried to run the migration. During the process, I ended up with a ForeignKeyConstraintException! I quickly scanned the schema to see what could have caused that, and didn't see anything obvious. The table I was trying to modify didn't have any foreign keys itself, and the column I was dropping was not a foreign key. Curious to understand what was happening, I started to dig into what this method call was doing. I saw that although you can add a column with SQLite's ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${columnType} statements, there's no support for removing a column out of the box. The library method I was using emulates dropping a column by doing the following: Rename the existing table into $tablename_old Creating a new table with all the existing columns, minus the one we don't want Copying all the data from $tablename_old to $tablename Dropping $tablename_old, since we don't need it anymore. This process seems to make a lot of sense - since we can't remove the column on its own, let's just make a new table with the structure we want and copy over the data that we want to keep. So why does this process not work? The Gotcha! If you read the SQlite documentation linked above closely, you might have noticed an important