SQL ALTER TABLE (Add, Drop, Modify)

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

ALTER TABLE lets you change an existing table’s structure — add, drop, or modify columns and constraints without losing data.

🔹 Basic Syntax

-- Add a new column
ALTER TABLE table_name
ADD column_name datatype [constraints];

-- Drop a column
ALTER TABLE table_name
DROP COLUMN column_name;

-- Modify a column (syntax varies by DBMS)
ALTER TABLE table_name
MODIFY column_name new_datatype [constraints]; -- MySQL, Oracle

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype; -- PostgreSQL, SQL Server

🔹 Examples

-- Add a column 'email' to 'employees'
ALTER TABLE employees
ADD email VARCHAR(100);

-- Drop 'salary' column from 'employees'
ALTER TABLE employees
DROP COLUMN salary;

-- Modify 'email' column to be NOT NULL (MySQL)
ALTER TABLE employees
MODIFY email VARCHAR(100) NOT NULL;

🔹 Notes

• Syntax for modifying columns varies by DBMS — always check your DBMS docs.
• Some DBMS don’t allow dropping columns easily (e.g., older versions of Oracle).
• Use ALTER TABLE carefully, as structure changes affect existing data.

🧠 Quick Recap

💡 ALTER TABLE is powerful — use it to adapt your table as requirements change.