Julia Evans

I’ve been doing a lot of SQLite migrations, and I’ve learned that this doesn’t work in SQLite.

CREATE TABLE newspapers (
    id INTEGER PRIMARY KEY,
    editor_id INTEGER,
    FOREIGN KEY (editor_id) REFERENCES users(id)
);
ALTER TABLE newspapers DROP COLUMN editor_id;

It fails with this error:

error in table newspapers after drop column:
unknown column "editor_id" in foreign key definition

Instead you have to create a new table, copy over the data, and rename it. The SQLite docs have a 12-step process for recreating tables like this, and there’s a great blog post by Simon Willison.