Learn SQL  /  Data Modification  /  ALTER TABLE

ALTER TABLE

ALTER TABLE changes the structure of a table that already exists — adding a column, renaming one, or removing it — without having to rebuild the whole table from scratch.

Adding a Column

R Code
ALTER TABLE students
ADD COLUMN email TEXT;
💡 Tip
A newly added column starts out NULL for every existing row, unless you specify a DEFAULT value in the ADD COLUMN statement.

Renaming a Column

R Code
ALTER TABLE students
RENAME COLUMN score TO exam_score;

Dropping a Column

R Code
ALTER TABLE students
DROP COLUMN email;

Renaming a Table

R Code
ALTER TABLE students
RENAME TO learners;

Advanced: DROP TABLE vs TRUNCATE vs DELETE

🚀 Advanced Example
These three sound similar but do very different things - know which one you actually want before running it.
R Code
DROP TABLE students;      -- deletes the table AND its structure entirely
TRUNCATE TABLE students;  -- empties all rows, keeps the table structure (not in SQLite)
DELETE FROM students;     -- removes rows one by one, keeps the structure, can be rolled back
⚠ Common Mistake
DROP TABLE removes the table definition itself - all columns, constraints, and data are gone, and this generally cannot be undone. Never run it against a production database without a verified backup.

Key Points

  • ALTER TABLE modifies an existing table's structure without needing to rebuild it.
  • ADD COLUMN, RENAME COLUMN, and DROP COLUMN are the most common ALTER TABLE operations.
  • DROP TABLE removes the entire table (structure and data); TRUNCATE empties rows but keeps the structure; DELETE removes rows individually and can be rolled back inside a transaction.