Can you rename a column in SQLite?

Yes, but only in SQLite 3.25.0+ (released September 2018). Use: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; For older versions, you must recreate the table with the new column name.

Can you change a column type in SQLite?

No. SQLite does not support ALTER COLUMN TYPE. You must recreate the table with the new column definition, copy the data, drop the old table, and rename the new one. SchemaLens generates this workaround automatically.

Can you drop a column in SQLite?

Yes, but only in SQLite 3.35.0+ (released March 2021). Use: ALTER TABLE table_name DROP COLUMN column_name; For older versions, recreate the table without the column.

Can you add a FOREIGN KEY in SQLite with ALTER TABLE?

No. SQLite cannot add FOREIGN KEY constraints via ALTER TABLE. You must either add the FK at table creation time or recreate the table with the constraint.

SQLite ALTER TABLE — Complete Guide to Adding, Renaming & Dropping Columns

What SQLite ALTER TABLE Supports

Unlike PostgreSQL, MySQL, or SQL Server, SQLite has a deliberately minimal ALTER TABLE implementation. As of SQLite 3.45.0, only these operations are supported:

RENAME TABLE — Rename the entire table.
ADD COLUMN — Add a new column (with restrictions).
DROP COLUMN — Remove a column (SQLite 3.35.0+, March 2021).
RENAME COLUMN — Rename a column (SQLite 3.25.0+, September 2018).

Notably, SQLite does not support:

Changing a column's data type
Adding a FOREIGN KEY constraint to an existing table
Adding a CHECK or UNIQUE constraint via ALTER TABLE
Changing a column's default value
Changing a column's nullability (NOT NULL ↔ NULL)

For any unsupported operation, you must use the table recreation workaround: create a new table with the desired schema, copy the data, drop the old table, and rename the new one.

➕ Add a Column

Add a new column to an existing SQLite table. Limited to the end of the column list with no PRIMARY KEY, FOREIGN KEY, or UNIQUE constraints.

-- Add a nullable column
ALTER TABLE users ADD COLUMN bio TEXT;

-- Add a column with a default value
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

-- Add a column with a constant expression default
ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;

-- NOT NULL is only allowed if a DEFAULT is also provided
ALTER TABLE users ADD COLUMN age INTEGER NOT NULL DEFAULT 0;

⚠️ Limitation: The new column is always appended to the end of the table. You cannot insert it in the middle. Also, you cannot add a column with a PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint using ALTER TABLE.

Compare your schema changes across environments. Compare Schemas →

✏️ Rename a Column

Rename an existing column. Requires SQLite 3.25.0 or later.

📌 Requires SQLite 3.25.0+ (September 2018). Check your version with SELECT sqlite_version();

-- Rename a column
ALTER TABLE users RENAME COLUMN full_name TO name;

⚠️ Safety Note: Renaming a column breaks any views, triggers, or application code that references the old name. Always search your codebase before deploying.

Find every reference before you rename. Compare Schemas →

🗑️ Drop a Column

Remove a column from a table. Requires SQLite 3.35.0 or later.

📌 Requires SQLite 3.35.0+ (March 2021). Check your version with SELECT sqlite_version();

-- Drop a column
ALTER TABLE users DROP COLUMN old_field;

⚠️ Safety Note: Dropping a column permanently deletes data. It can also break views and triggers. SchemaLens detects when a dropped column is referenced by a view and warns you before you deploy.

Check for view dependencies before dropping. Compare Schemas →

🔄 Change Column Type — Table Recreation Workaround

SQLite does not support ALTER COLUMN TYPE. Use this safe table-recreation pattern.

-- Step 1: Begin transaction
BEGIN TRANSACTION;

-- Step 2: Create the new table with the desired schema
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT,          -- changed from VARCHAR(255)
  email TEXT NOT NULL,
  age INTEGER         -- changed from TEXT
);

-- Step 3: Copy data from old table (cast if needed)
INSERT INTO users_new (id, name, email, age)
  SELECT id, name, email, CAST(age AS INTEGER)
  FROM users;

-- Step 4: Verify the data looks correct
-- SELECT * FROM users_new LIMIT 5;

-- Step 5: Drop the old table
DROP TABLE users;

-- Step 6: Rename the new table
ALTER TABLE users_new RENAME TO users;

-- Step 7: Recreate indexes, views, and triggers
CREATE INDEX idx_users_email ON users(email);

-- Step 8: Commit
COMMIT;

💡 Pro Tip: Use PRAGMA foreign_keys = OFF; before the recreation if other tables reference this one, then PRAGMA foreign_keys = ON; after. Alternatively, update referencing tables to point to the new table before dropping the old one.

⚠️ Critical: Do not forget to recreate indexes, views, and triggers after renaming. They are tied to the old table and will be lost when you DROP TABLE. Always back up your database before running table recreation.

SchemaLens generates this workaround automatically. Compare Schemas →

🔗 Add a Foreign Key — Table Recreation Workaround

SQLite cannot add FOREIGN KEY constraints via ALTER TABLE. Recreate the table with the constraint.

-- Step 1: Disable foreign key checks during migration
PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;

-- Step 2: Create new table with the foreign key
CREATE TABLE orders_new (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total REAL,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

-- Step 3: Copy data
INSERT INTO orders_new SELECT * FROM orders;

-- Step 4: Replace old table
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

COMMIT;

-- Step 5: Re-enable foreign key checks
PRAGMA foreign_keys = ON;

-- Step 6: Verify
PRAGMA foreign_key_check;

⚠️ Safety Note: PRAGMA foreign_keys = OFF only affects the current connection. If your application has other connections open, they may still enforce foreign keys. Perform this operation during a maintenance window or with the application stopped.

Find missing foreign keys across environments. Compare Schemas →

SQLite ALTER TABLE Quick Reference

ALTER TABLE ... RENAME TO ... — Rename table. ✅ Always supported.
ALTER TABLE ... ADD COLUMN ... — Add column (with limitations). ✅ Always supported.
ALTER TABLE ... DROP COLUMN ... — Drop column. ✅ 3.35.0+
ALTER TABLE ... RENAME COLUMN ... — Rename column. ✅ 3.25.0+
ALTER TABLE ... ALTER COLUMN ... — Change type/default/nullability. ❌ Not supported.
ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY — Add FK. ❌ Not supported.
ALTER TABLE ... ADD CONSTRAINT ... CHECK — Add CHECK. ❌ Not supported.

When to Use the Table Recreation Workaround

Use the recreation pattern whenever you need to:

Change a column's data type
Change a column's nullability (add or remove NOT NULL)
Change a column's default value
Add a FOREIGN KEY, CHECK, or UNIQUE constraint
Reorder columns (cosmetic, but sometimes desired)
Drop a column on SQLite < 3.35.0
Rename a column on SQLite < 3.25.0

SchemaLens's diff engine detects these unsupported changes and automatically generates the table recreation script with transaction safety, data copy, and index recreation.

SQLite ALTER TABLE — Complete Guide

What SQLite ALTER TABLE Supports

➕ Add a Column

✏️ Rename a Column

🗑️ Drop a Column

🔄 Change Column Type — Table Recreation Workaround

🔗 Add a Foreign Key — Table Recreation Workaround

SQLite ALTER TABLE Quick Reference

When to Use the Table Recreation Workaround

Related Migration Recipes

🔗 Add a Foreign Key

⚡ Add an Index

📖 All Migration Recipes