SQL ALTER TABLE

The ALTER TABLE statement modifies an existing table by adding, deleting, or modifying columns and constraints.

 

Key Features of SQL ALTER TABLE

• Add new columns to a table.
• Modify existing columns (e.g., change data type, constraints).
• Drop columns or constraints.
• Rename columns or the table.

 

SQL ALTER TABLE Syntax

ALTER TABLE table_name 
ADD column_name datatype constraint;

ALTER TABLE table_name 
MODIFY column_name new_datatype;

ALTER TABLE table_name 
DROP COLUMN column_name;

 

Example: Adding a Column

ALTER TABLE Employees 
ADD Email VARCHAR(100);

• Adds a new column Email to the Employees table.

 

Example: Modifying a Column

ALTER TABLE Employees 
MODIFY Salary DECIMAL(12,2);

• Changes the Salary column to allow 12 digits with 2 decimal places.

 

Example: Dropping a Column

ALTER TABLE Employees 
DROP COLUMN Age;

• Removes the Age column from the Employees table.

 

Example: Renaming a Column

ALTER TABLE Employees 
RENAME COLUMN Name TO FullName;

• Renames the Name column to FullName.

 

Example: Renaming a Table

ALTER TABLE Employees 
RENAME TO Staff;

• Renames the Employees table to Staff.

 

Summary

• ALTER TABLE is used to modify table structure.
• Can add, modify, drop, or rename columns and tables.
• Use carefully to prevent data loss.