Topic 5 of 52 · Beginner
ALTER TABLE – DROP COLUMN
Permanently remove a column and all its values.
01ALTER TABLE – DROP COLUMN
Module 02 – Database & Table Management (DDL)
02What is it?
DROP COLUMN deletes a column from the table definition. Every value stored in that column, in every row, is gone permanently. The rows themselves remain.
03Why do we need it?
Unused columns waste storage, confuse users and may hold sensitive data that must be removed (for example, an old PAN or Aadhaar column after a privacy review).
04Syntax
ALTER TABLE table_name DROP [COLUMN] column_name;05Parameter explanation
- column_name
- The column to remove. It must exist (MySQL has no IF EXISTS here).
- COLUMN
- Optional keyword.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL checks hire_date exists and is not the table's only column.
- 2.The column is removed from the definition and its data is discarded.
- 3.Indexes using only that column are dropped too. The 5 rows still exist with 4 columns.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
During a schema clean-up, a data engineer confirms (via query logs) that legacy_code hasn't been read in 6 months, takes a backup, then runs ALTER TABLE ... DROP COLUMN legacy_code.
12Common mistakes
ALTER TABLE employees DROP COLUMN phone;
phone doesn't exist → 'Can't DROP; check that column exists'.
Check columns first with DESCRIBE employees;
ALTER TABLE employees DELETE COLUMN salary;
DELETE is a DML command for rows, not columns.
ALTER TABLE employees DROP COLUMN salary;
UPDATE employees SET salary = NULL; -- thinking it removes the column
This only empties values; the column still exists.
ALTER TABLE employees DROP COLUMN salary;
13Important comparisons
| Command | Removes |
|---|---|
| ALTER TABLE t DROP COLUMN c | One column (all its values) |
| DELETE FROM t WHERE ... | Some rows |
| TRUNCATE TABLE t | All rows, keeps table |
| DROP TABLE t | The whole table |
14Practical exercises
Beginner1. Drop hire_date from employees.
Reveal solution (15)
Four columns remain.
Beginner2. Drop salary and confirm the remaining column names.
Reveal solution (15)
In MySQL you would run DESCRIBE employees.
Beginner3. Add a column notes TEXT, then drop it.
Reveal solution (15)
Table returns to its original shape.
Intermediate4. Copy salary into a backup table, then drop it from employees.
Reveal solution (15)
Always back up before destructive DDL.
Intermediate5. Drop two columns: hire_date and salary.
Reveal solution (15)
MySQL also accepts DROP COLUMN a, DROP COLUMN b in one statement.
Intermediate6. Show that a query using a dropped column fails.
Reveal solution (15)
Expected error: no such column.
Advanced7. Restore a dropped salary column from salary_backup.
Reveal solution (15)
Recovery is only possible because of the backup.
Advanced8. Count how many non-NULL values would be lost before dropping hire_date.
Reveal solution (15)
A pre-check quantifies the impact (here 5).
16Key takeaways
- DROP COLUMN permanently deletes a column's data.
- Rows survive; only that column goes.
- Back up and check dependencies (views, code) first.
- DDL auto-commits in MySQL — no ROLLBACK.
17Knowledge check
1. After DROP COLUMN, how many rows does the table have?
2. Can you ROLLBACK a DROP COLUMN in MySQL?
3. Which statement removes a column?
Interview questions
What happens to an index when its column is dropped?
A single-column index on it is removed; a composite index loses that column, and if no columns remain the index is dropped.
Lesson test
Take an 8-question test on “ALTER TABLE – DROP COLUMN” — concepts, output prediction, bug spotting and interview questions.