The SQL ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is a powerful command that helps manage the structure of a database table without losing the data.


ALTER TABLE Employees
ADD Email VARCHAR(255);

Modifying a Column:

You can change the data type of a column using the ALTER TABLE statement. This is useful when you want to increase the size of a column or change its datatype.


ALTER TABLE Employees
MODIFY Email VARCHAR(500);

Dropping a Column:

To remove a column from a table, use the DROP COLUMN clause. This action is irreversible, so ensure that the column is no longer needed.


ALTER TABLE Employees
DROP COLUMN Email;

Renaming a Column:

Sometimes, renaming a column can improve clarity or reflect changes in the data model. Use the RENAME COLUMN clause to achieve this.


ALTER TABLE Employees
RENAME COLUMN Email TO ContactEmail;

Adding Constraints:

Constraints can be added to ensure data integrity. For example, adding a NOT NULL constraint to prevent null values in a column.


ALTER TABLE Employees
ADD CONSTRAINT chk_Email CHECK (Email IS NOT NULL);

Dropping Constraints:

Constraints that are no longer necessary can be removed using the DROP CONSTRAINT clause.


ALTER TABLE Employees
DROP CONSTRAINT chk_Email;

Changing Column Order:

While SQL standards do not support changing the order of columns, some databases provide proprietary syntax for this operation.


-- Example for MySQL
ALTER TABLE Employees
MODIFY COLUMN Email VARCHAR(255) AFTER LastName;

Altering Table Properties:

In some databases, you can alter table properties such as storage engines, partitioning, etc., using the ALTER TABLE command.


-- Example for MySQL
ALTER TABLE Employees
ENGINE = InnoDB;