MySQL ALTER TABLE Statement
MySQL ALTER TABLE Statement
The ALTER TABLE statement in MySQL is used to modify the structure of an existing table without deleting its data.
- It is one of the most important SQL commands for real projects, exams, and interviews.
What is ALTER TABLE?
ALTER TABLE allows you to:
Add new columns
Modify existing columns
Drop columns
Add / remove constraints
Rename columns or tables
Data inside the table remains intact (unless explicitly dropped).
Basic Syntax
1 2 | ALTER TABLE table_name action; |
ADD Columns
The ADD keyword is used with ALTER TABLE to add new columns to an existing table.
You can add one column or multiple columns in a single statement.
New columns can have data types, default values, and constraints.
Syntax (Single Column)
1 2 | ALTER TABLE table_name ADD column_name datatype; |
Example
1 2 | ALTER TABLE employees ADD age INT; |
ADD Column with DEFAULT Value
A default value is automatically assigned if no value is provided during insert.
1 2 | ALTER TABLE employees ADD status VARCHAR(20) DEFAULT 'active'; |
ADD Column with NOT NULL
The NOT NULL constraint ensures the column cannot store NULL values.
1 2 | ALTER TABLE employees ADD email VARCHAR(100) NOT NULL; |
ADD Column at Specific Position
MySQL allows placing a column at a specific position using FIRST or AFTER.
1 2 | ALTER TABLE employees ADD joining_date DATE AFTER name; |
ADD Multiple Columns
Multiple columns can be added in one statement.
1 2 3 4 5 | ALTER TABLE employees ADD ( salary DECIMAL(10,2), department VARCHAR(50) ); |
MODIFY Column (Change Datatype / Constraint)
Change Datatype
1 2 | ALTER TABLE students MODIFY age SMALLINT; |
Add NOT NULL
1 2 | ALTER TABLE students MODIFY name VARCHAR(50) NOT NULL; |
RENAME Column (MySQL 8+)
1 2 | ALTER TABLE students RENAME COLUMN city TO location; |
DROP Column
1 2 | ALTER TABLE students DROP COLUMN age; |
- Column and its data are permanently removed
ADD Constraints Using ALTER TABLE
Add-PRIMARY KEY
1 2 | ALTER TABLE students ADD PRIMARY KEY (student_id); |
Add-UNIQUE
1 2 | ALTER TABLE students ADD CONSTRAINT uq_email UNIQUE (email); |
Add-FOREIGN KEY
1 2 3 4 | ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id); |
Add-CHECK
1 2 3 | ALTER TABLE students ADD CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100); |
DROP Constraints
Drop-PRIMARY KEY
1 2 | ALTER TABLE students DROP PRIMARY KEY; |
Drop-FOREIGN KEY
1 2 | ALTER TABLE employees DROP FOREIGN KEY fk_dept; |
Drop-UNIQUE
1 2 | ALTER TABLE users DROP INDEX uq_email; |
RENAME Table
1 2 | ALTER TABLE students RENAME TO learners; |
ALTER TABLE vs UPDATE (Interview)
| Feature | ALTER TABLE | UPDATE |
|---|---|---|
| Affects | Table structure | Table data |
| Changes columns | Yes | No |
| Changes rows | No | Yes |
| Common use | Schema change | Data change |
Common Errors & Warnings
- Dropping columns accidentally
- Changing datatype causing data loss
- Adding NOT NULL when NULL data exists
- Forgetting constraint names
Interview Questions & MCQs (Very Important)
Q1. What is ALTER TABLE used for?
A) Insert data
B) Delete records
C) Modify table structure
D) Select data
Answer: C
Q2. Which command adds a column?
ALTER TABLE t ADD column datatype;
A) Correct
B) Incorrect
Answer: A
Q3. Which command removes a column?
A) DELETE COLUMN
B) DROP COLUMN
C) REMOVE COLUMN
D) CLEAR COLUMN
Answer: B
Q4. Can ALTER TABLE delete data?
A) Yes
B) No
Answer: No
(Except dropped columns)
Q5. Which command renames a table?
ALTER TABLE old_name RENAME TO new_name;
A) Correct
B) Incorrect
Answer: A
Q6. Which command adds a FOREIGN KEY?
A) ADD KEY
B) ADD CONSTRAINT
C) ADD INDEX
D) ADD CHECK
Answer: B
Real-Life Use Cases
- Adding new fields to applications
- Changing data types
- Enforcing new constraints
- Database version upgrades
- Schema maintenance
Summary
ALTER TABLEmodifies existing table structureUsed to ADD, MODIFY, DROP, RENAME columns
Can manage constraints
Does not affect existing data (mostly)
Very important for SQL exams & interviews
