SQL ALTER TABLE Statement

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. This allows you to add, delete, or change columns and constraints after a table has been created.

ALTER TABLE: Adding and Deleting Columns

Adding a Column

To add a column to a table, use the ADD clause. You specify the new column's name and its data type.

Syntax


ALTER TABLE table_name
ADD column_name data_type;

Example: Adding an Email Column

Syntax


ALTER TABLE Customers
ADD Email VARCHAR(255);

Output

Adds an 'Email' column to the 'Customers' table. New rows will have a NULL value for 'Email' unless a default value is specified.

Deleting a Column

To delete a column, use the DROP COLUMN clause. Be careful; this is a destructive operation—the data in the column will be lost.

Syntax


ALTER TABLE table_name
DROP COLUMN column_name;

Example: Deleting an Email Column

Syntax


ALTER TABLE Customers
DROP COLUMN Email;

Output

The 'Email' column is removed from the 'Customers' table. All data in that column is lost.

ALTER TABLE: Renaming and Modifying Columns

Renaming a Column

To rename a column, use the RENAME COLUMN clause (the exact syntax may vary slightly depending on your database system). For SQL Server you need to use the `sp_rename` stored procedure.

Syntax (Most Systems)


ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Syntax (SQL Server)


EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';

(Example using SQL Server's `sp_rename` stored procedure would be included here)

Modifying Column Data Type

To change a column's data type, use ALTER COLUMN (SQL Server/MS Access) or MODIFY COLUMN (MySQL/Oracle prior to 10g) or `MODIFY` (Oracle 10g and later). Be cautious: data might be lost or require conversion if it's not compatible with the new data type.

Syntax (SQL Server/MS Access)


ALTER TABLE table_name ALTER COLUMN column_name datatype;

Syntax (MySQL/Oracle prior to 10g)


ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Syntax (Oracle 10g and later)


ALTER TABLE table_name MODIFY column_name datatype;

Example: Adding a DateOfBirth Column

This adds a 'DateOfBirth' column (date data type) to the 'Persons' table (assuming a 'Persons' table already exists).

Syntax


ALTER TABLE Persons ADD DateOfBirth DATE;

Output

A 'DateOfBirth' column of type DATE is added to the 'Persons' table. Existing rows will have a NULL value in this new column.

ID	LastName	FirstName	Address	City	DateOfBirth
1	Hansen	Ola	Timoteivn 10	Sandnes	NULL
2	Svendson	Tove	Borgvn 23	Sandnes	NULL
3	Pettersen	Kari	Storgt 20	Stavanger	NULL

Changing a Column's Data Type

This changes the data type of the 'DateOfBirth' column to 'YEAR'.

Syntax


ALTER TABLE Persons ALTER COLUMN DateOfBirth YEAR;

Output

The 'DateOfBirth' column's data type is changed to 'YEAR'. Existing data should be compatible with this change. If not, an error will result.

Deleting a Column

This removes the 'DateOfBirth' column.

Syntax


ALTER TABLE Persons DROP COLUMN DateOfBirth;

Output

The 'DateOfBirth' column and its data are deleted from the 'Persons' table.

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger