SQL ALTER TABLE Statement

The ALTER TABLE statement in SQL is a powerful tool for modifying the structure of an existing table in your database. It allows you to make changes to your table's design after it has been created.

ALTER TABLE: Definition and Usage

ALTER TABLE enables you to add new columns, delete existing columns, or modify the properties of existing columns (such as data types or constraints). It's important to understand the implications of your changes, particularly when altering data types or removing columns, as this can affect existing data; you might need to perform data conversions or could potentially lose data.

Adding Columns

Use the ADD clause to add a new column to your table. You must specify the name and data type of the new column.

ALTER TABLE table_name
ADD column_name data_type;
      

Example: Adding an Email Column

ALTER TABLE Customers
ADD Email VARCHAR(255);
      

(A new column named 'Email' with a VARCHAR(255) data type is added to the 'Customers' table.  Existing rows will initially have NULL values in this new column.)
      

Deleting Columns

Use DROP COLUMN to remove a column from a table. Be extremely careful with this command, as the data in the deleted column is lost permanently.

ALTER TABLE table_name
DROP COLUMN column_name;
      

Example: Deleting the Email Column

ALTER TABLE Customers
DROP COLUMN Email;
      

(The 'Email' column is removed from the 'Customers' table. The data in this column is permanently lost.)
      

Changing Column Data Types

Use ALTER COLUMN to change a column's data type. Ensure that your existing data is compatible with the new data type to avoid errors or data loss.

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;
      

Example: Changing the BirthDate Data Type

ALTER TABLE Employees
ALTER COLUMN BirthDate YEAR;
      

(The 'BirthDate' column's data type in the 'Employees' table is changed to 'YEAR'.  Existing data must be compatible with this change.)