ALTER TABLE

What Is ALTER TABLE?

ALTER TABLE is used to modify the structure of an existing table, such as adding, changing, or removing columns.

Adding a Column (ADD)

ALTER TABLE EMPLOYEE
ADD COLUMN email VARCHAR(100);

ALTER TABLE EMPLOYEE
ADD COLUMN date_of_birth DATE AFTER name;

Changing a Data Type (MODIFY)

ALTER TABLE EMPLOYEE
MODIFY COLUMN salary DECIMAL(12, 2);

ALTER TABLE EMPLOYEE
MODIFY COLUMN name VARCHAR(150) NOT NULL;

Dropping a Column (DROP)

ALTER TABLE EMPLOYEE
DROP COLUMN phone;

Renaming a Column

ALTER TABLE EMPLOYEE
RENAME COLUMN address TO home_address;

Renaming a Table

ALTER TABLE EMPLOYEE
RENAME TO EMPLOYEE_V2;

RENAME TABLE EMPLOYEE_V2 TO EMPLOYEE;

Complete Example

-- Add an email column
ALTER TABLE EMPLOYEE
ADD COLUMN email VARCHAR(100);

-- Change the salary type
ALTER TABLE EMPLOYEE
MODIFY COLUMN salary DECIMAL(12, 2) NOT NULL;

-- Rename a column
ALTER TABLE EMPLOYEE
RENAME COLUMN phone TO mobile_number;

Important Considerations

• Always back up your data before ALTER TABLE in production
• Change data types gradually on tables with a lot of data
• Check application dependencies before renaming a column or table

After understanding ALTER TABLE, let's learn the differences between DROP & TRUNCATE in the next chapter.