Beacon Robotics' schema will keep evolving, a new column needed on employees, a column that's outlived its usefulness, a type that needs widening. ALTER TABLE changes an existing table's structure directly, without dropping and recreating it, and losing the data already inside.
Learning Objectives
- Add and drop a column with ALTER TABLE.
- Change an existing column's type or constraint.
- Know where MODIFY COLUMN and ALTER COLUMN syntax splits across databases.
Adding a Column
email is added to every existing row too, with a NULL value, since no existing row has one yet. (SQL Server and Oracle accept ADD email VARCHAR(100) without the word COLUMN as well, COLUMN is optional there, but including it reads clearly everywhere.)
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
Dropping a Column
The column, and every value stored in it, is gone, for every row, immediately. SQLite only added DROP COLUMN support in version 3.35 (2021), older SQLite versions need a more involved workaround (recreate the table without the column, copy the data across).
ALTER TABLE employees DROP COLUMN email;
Modifying a Column: Where Syntax Splits
| Database | Change an Existing Column's Type |
|---|---|
| MySQL / MariaDB | ALTER TABLE t MODIFY COLUMN col INT; |
| PostgreSQL | ALTER TABLE t ALTER COLUMN col TYPE INT; |
| SQL Server | ALTER TABLE t ALTER COLUMN col INT; |
| Oracle | ALTER TABLE t MODIFY col INT; |
| SQLite | Not supported directly, recreate the table instead |
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12,2);
MODIFY COLUMN (MySQL/MariaDB, and MODIFY without COLUMN on Oracle) and ALTER COLUMN (PostgreSQL, SQL Server) are the two real patterns, genuinely different keywords for the identical idea. SQLite's limited ALTER TABLE support (effectively just renaming and adding columns) is a direct consequence of its minimal, embedded design.
Renaming a Column
Most of this course's databases now share this exact RENAME COLUMN syntax; SQL Server instead uses its own sp_rename system procedure, and older MySQL (pre-8.0) needed the more verbose CHANGE COLUMN syntax instead.
-- PostgreSQL / SQLite / MySQL 8+ / MariaDB ALTER TABLE employees RENAME COLUMN email TO contact_email;
Try It Yourself
Write a statement that adds a phone VARCHAR(20) column to employees, on PostgreSQL.
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
Frequently Asked Questions
Does adding a column with a NOT NULL constraint work on a table that already has rows?
Not directly, in most databases, existing rows would have no value for the new NOT NULL column, violating the constraint immediately. A DEFAULT value (covered in Module 8) is typically required alongside NOT NULL when adding to a non-empty table.
Can multiple columns be added in a single ALTER TABLE statement?
Yes, on most of these databases, comma-separating multiple ADD COLUMN clauses in one ALTER TABLE statement, rather than running several separate statements. Exact syntax for combining them varies slightly by database.
Common Beginner Mistakes
Using MODIFY COLUMN on PostgreSQL, or ALTER COLUMN on MySQL
Neither works on the other database, this is the exact split the table above exists to flag. Check which pattern applies before writing the statement.
Expecting DROP COLUMN to work on an older SQLite version
It was only added in SQLite 3.35 (2021). Older versions need the table-recreation workaround instead, directly, DROP COLUMN simply isn't recognized.
Best Practices
- Check the exact MODIFY COLUMN vs ALTER COLUMN syntax for the target database before writing the statement.
- Plan for a DEFAULT value when adding a NOT NULL column to a table that already has data.
- Treat DROP COLUMN as irreversible, the data in it is genuinely gone, same caution as DROP TABLE.
Interview Questions
What's the difference between MODIFY COLUMN and ALTER COLUMN?
They're two different keywords for the identical idea, changing an existing column's type or definition. MySQL and MariaDB use MODIFY COLUMN (Oracle uses MODIFY without the word COLUMN); PostgreSQL and SQL Server use ALTER COLUMN. There's no ANSI-standard keyword shared across all of them.
Why might adding a NOT NULL column fail on a table that already contains rows?
Existing rows have no value for the brand-new column, which would violate a NOT NULL constraint immediately for every one of them. Pairing the new column with a DEFAULT value, so existing rows get a real value automatically, is the typical fix.
Summary
ALTER TABLE adds, drops, or modifies a column on an existing table, without losing the data already there. MODIFY COLUMN (MySQL/MariaDB/Oracle) and ALTER COLUMN (PostgreSQL/SQL Server) are the two real syntax patterns for changing a column's type, SQLite's support remains the most limited of the six.
That completes Module 7, Database and Tables: CREATE and DROP DATABASE, the honest reality of BACKUP DATABASE across different systems, and CREATE, DROP, and ALTER TABLE, including the real statements behind Beacon Robotics' own schema.
What's Next?
Module 8, Constraints, is next: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT in full depth, the rules this module's CREATE TABLE statements only partly enforced so far.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.