Change column name in sqlite when other tables have foreign keys to it

Question

I have a table named groups, and I want to rename one of its columns. Is was ok, so far. I know sqlite doesn't support renaming columns, so I did:

ALTER TABLE groups RENAME to tmp_groups;

CREATE TABLE groups(
    _ID integer primary key autoincrement,
    new_column_name integer
);

INSERT INTO groups(_ID, new_column_name) SELECT _ID, old_column_name FROM tmp_groups;

DROP TABLE tmp_groups;

But, when I drop the table tmp_groups, the table members, that had a foreign key with ON DELETE CASCADE has its records deleted as well, so I had to do the following:

1. Create a table tmp_members with the same columns as members, and without the foreign key;
2. Insert the records from members in tmp_members;
3. Drop the table members;
4. Run the code from the first part (with the groups table);
5. Re-create the table members with its foreign key;
6. Insert in members data from tmp_members;

Man, that was tiring! Its too much code to simply rename a column;

Is there any simpler way to handle this constraint problem, or is this the "sqlite way"?

Answer 1

For historical reasons, SQLite allows to disable foreign key constraints (and this is even the default currently).

Just run PRAGMA foreign_keys = off before doing the groups table stuff.

---

It would also be possible to rename a column by using PRAGMA writable_schema, but you should do this only if you know what you're doing.