SQL ALTER TABLE — Add, Drop, and Rename Columns
How to use ALTER TABLE to add columns, drop columns, rename columns, and modify constraints. Covers PostgreSQL, MySQL, and SQLite differences.
DDL Commands
Detailed Explanation
Modifying Tables with ALTER TABLE
As requirements change, you will need to modify existing tables without dropping and recreating them. ALTER TABLE is the DDL command for structural changes.
Common Operations
Adding a Column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);
Dropping a Column
ALTER TABLE employees
DROP COLUMN phone;
Renaming a Column
ALTER TABLE employees
RENAME COLUMN old_name TO new_name;
Adding a Constraint
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id);
Dialect-Specific Behavior
- PostgreSQL supports all standard ALTER operations and also allows
ALTER COLUMN ... SET DATA TYPEandALTER COLUMN ... SET DEFAULT. - MySQL uses
MODIFY COLUMNto change a column's data type andCHANGE COLUMNto rename and retype simultaneously. - SQLite has very limited ALTER TABLE support: only
ADD COLUMN(all versions),RENAME COLUMN(3.25+), andDROP COLUMN(3.35+). For other changes, you must recreate the table.
Migration Best Practices
Always run ALTER statements inside a transaction when your database supports transactional DDL (PostgreSQL does; MySQL does not for most DDL). Test on a staging environment before production.
Use Case
You need to add a new column to an existing production table — for example, adding a phone_number field to your users table — without losing existing data.