SQLite - ALTER Command

SQLite - ALTER TABLE Command

The ALTER TABLE command is used to modify the structure (schema) of an existing table. While historically limited in SQLite, modern versions provide a powerful set of operations for evolving your database schema without losing data.

Supported operations in modern SQLite include:

• RENAME TO: Renames an entire table.
• ADD COLUMN: Adds a new column to a table.
• RENAME COLUMN: Renames an existing column (available since SQLite 3.25.0+).
• DROP COLUMN: Removes an existing column (available since SQLite 3.35.0+).

Always check your SQLite version (SELECT sqlite_version();) before using newer features like DROP COLUMN or RENAME COLUMN.

Syntax and Examples

1. Rename a Table

ALTER TABLE current_table_name RENAME TO new_table_name;

Example: ALTER TABLE COMPANY RENAME TO ORGANIZATIONS;

2. Add a Column

ALTER TABLE table_name ADD COLUMN new_column_definition;

It’s a best practice to provide a DEFAULT value, especially if you add a NOT NULL column to a table that already has data.

-- Add a new column with a default value to avoid NULLs in existing rows.
ALTER TABLE EMPLOYEES ADD COLUMN IS_ACTIVE INTEGER NOT NULL DEFAULT 1;

3. Rename a Column

-- Note: Requires SQLite 3.25.0 or newer.
ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name;

Example: ALTER TABLE EMPLOYEES RENAME COLUMN SALARY TO ANNUAL_SALARY;

4. Drop a Column

-- Note: Requires SQLite 3.35.0 or newer.
ALTER TABLE table_name DROP COLUMN column_name;

Example: ALTER TABLE EMPLOYEES DROP COLUMN TEMP_NOTES;

The Manual Method for Complex Changes

For older SQLite versions or for changes not directly supported (like changing a column’s type or constraints), you must perform a manual migration. The process is:

1. Start a transaction: BEGIN TRANSACTION;
2. Create a new table with the desired schema: CREATE TABLE new_table (...)
3. Copy the data from the old table to the new one: INSERT INTO new_table SELECT ... FROM old_table
4. Drop the old table: DROP TABLE old_table
5. Rename the new table to the original name: ALTER TABLE new_table RENAME TO old_table
6. Re-create any indexes or triggers that were on the old table.
7. Commit the transaction: COMMIT;

Important Considerations

• Transactions: Always wrap schema changes in a transaction (BEGIN/COMMIT). This makes the entire operation atomic, ensuring that if any step fails, the database is rolled back to its original state.
• Dependencies: Be aware that renaming or dropping tables and columns will break any views, triggers, or application code that relies on them. These must be updated manually.
• Performance: ALTER TABLE on very large tables can be slow and lock the table, impacting application performance during the migration.