SQL ALTER TABLE
beginnerbasics
2 min read
What is ALTER TABLE?
ALTER TABLE modifies an existing table's structure. You can add or remove columns, change data types, rename the table, and manage constraints.
Syntax
ALTER TABLE table_name ADD COLUMN column_name data_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type;When to Use
- Adding a new field as requirements evolve
- Dropping unused columns
- Renaming columns for clarity
- Adding or removing constraints
Key Points
- Non-destructive — Adding a column does not affect existing data; it fills the new column with NULL or a default.
- Dropping Columns — Removing a column deletes its data permanently. Use with caution.
- Constraints — You can add or drop constraints:
ALTER TABLE t ADD CONSTRAINT ... UNIQUE (col);. - Rename —
ALTER TABLE old_name RENAME TO new_name;renames the entire table. - Locking — Some ALTER TABLE operations lock the table briefly. Plan schema changes during low-traffic periods.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL ALTER TABLE Challenge
Write a query that solve this task: add a new nullable phone number column.
Expected result
The students table now has a phone column. Existing rows have NULL for phone.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is ALTER TABLE?
Initializing database...Each run starts from fresh sample data.
More Examples
Drop a column
Remove the grade column from the students table.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can I add a NOT NULL column to a table with existing data?
Only if you provide a DEFAULT value. Otherwise existing rows would violate the NOT NULL constraint.
Does ALTER TABLE lock the table?
Some operations acquire a brief lock. Adding a column with a default is fast in modern PostgreSQL, but changing a column type may rewrite the table.