What is the ALTER Statement in SQL ?
Answer : The SQL ALTER statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Using ALTER, we can:
- Add a new column
- Modify a column
- Rename a column
- Drop a column
1. Add a Column
Suppose we have an Employees table:
| Employee_ID | Employee_Name | Salary |
|---|---|---|
| INT | VARCHAR(100) | DECIMAL(10,2) |
To add a Department column:
ALTER TABLE Employees
ADD Department VARCHAR(30);
After adding the column:
| Employee_ID | Employee_Name | Salary | Department |
|---|---|---|---|
| INT | VARCHAR(100) | DECIMAL(10,2) | VARCHAR(30) |
2. Drop a Column
To remove the Salary column:
ALTER TABLE Employees
DROP COLUMN Salary;3. Rename a Column
The syntax varies by database. For example, in PostgreSQL/MySQL:
ALTER TABLE Employees
RENAME COLUMN Employee_Name TO Name;If we want to change the Salary column from INT to DECIMAL(10,2):
ALTER TABLE Employees
MODIFY Salary DECIMAL(10,2);
Important Points :
When we add a column using ALTER TABLE statement then it automatically adds those columns to the end of the table and updates values for each records in that column with NULL.
When we delete a column from a table, column and all the data it contains are deleted.
You cannot delete a column that has a CHECK constraint. You must first delete the constraint.
You cannot delete the column that has PRIMARY KEY or FOREIGN KEY constraint or other dependencies.
