SQLite ALTER TABLE: Add, Rename and Drop Columns

Add a column with a useful default

Run this self-contained example once in a fresh example database. The existing row gets active = 1. Adding a NOT NULL column requires a non-NULL default; adding a primary-key or unique column this way is restricted.

CREATE TABLE contacts(id INTEGER PRIMARY KEY,name TEXT); INSERT INTO contacts VALUES(1,'Asha'); ALTER TABLE contacts ADD COLUMN active INTEGER NOT NULL DEFAULT 1; SELECT * FROM contacts;

Rename a column

This separate example returns display_name = Asha. SQLite updates dependent schema references where supported; ambiguous references can cause the rename to fail.

CREATE TABLE contacts(id INTEGER PRIMARY KEY,name TEXT); INSERT INTO contacts VALUES(1,'Asha'); ALTER TABLE contacts RENAME COLUMN name TO display_name; SELECT display_name FROM contacts;

Understand drop failures

An indexed column or one referenced by a constraint, trigger or view cannot simply be dropped. Inspect the dependency and decide whether it should change. Do not bypass schema checks with writable_schema. For changes requiring a rebuild, use the workspace migration preview and review its generated SQL before applying.

Match the SQLite version

Supported alterations vary by version. SQLite 3.53 added setting and dropping NOT NULL through ALTER COLUMN. Check the actual engine before using new syntax. SQLite Lab manages atomic runs, so do not wrap editor scripts in BEGIN or COMMIT.

SELECT sqlite_version() AS sqlite_version;