SQL ALTER TABLE

SQL ALTER TABLE Statement

The ALTER TABLE statement serves to incorporate, remove, or amend columns within an established table.

Additionally, it facilitates the addition or removal of diverse constraints on the existing table.

ALTER TABLE – ADD Column

To include a column in a table, utilize the following syntax:

ALTER TABLE table_name ADD column_name datatype;

This SQL statement adds a “Email” column to the “Customers” table:

Example

ALTER TABLE Customers ADD Email varchar(255);

ALTER TABLE – DROP COLUMN

To remove a column from a table, employ the following syntax (bearing in mind that certain database systems may not permit column deletion):

ALTER TABLE table_name DROP COLUMN column_name;

This SQL statement removes the “Email” column from the “Customers” table:

Example

ALTER TABLE Customers DROP COLUMN Email;

ALTER TABLE – RENAME COLUMN

To change the name of a column in a table, employ the following syntax:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

To alter the name of a column in a table within SQL Server, utilize the following syntax:

SQL Server:

EXEC sp_rename ‘table_name.old_name‘,  ‘new_name‘, ‘COLUMN’;

ALTER TABLE – ALTER/MODIFY DATATYPE

To modify the data type of a column in a table, employ the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Oracle 10G and later:

ALTER TABLE table_name MODIFY column_name datatype;

SQL ALTER TABLE Example

Examine 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

Next, we aim to include a column named “DateOfBirth” in the “Persons” table, employing the subsequent SQL statement:

ALTER TABLE Persons ADD DateOfBirth date;

The new column, “DateOfBirth,” is designated as a date type, indicating its purpose of storing dates. Data types define the kind of data a column can accommodate. For an exhaustive list of available data types in MS Access, MySQL, and SQL Server, refer to our comprehensive Data Types reference.

The structure of the “Persons” table will be as follows:

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

Change Data Type Example

We now intend to modify the data type of the column labeled “DateOfBirth” in the “Persons” table.

We employ the subsequent SQL statement:

ALTER TABLE Persons ALTER COLUMN DateOfBirth year;

Observe that the “DateOfBirth” column has been updated to be of type “year,” intended to store a year in either a two- or four-digit format.

DROP COLUMN Example

Subsequently, we aim to remove the column labeled “DateOfBirth” from the “Persons” table.

We employ the following SQL statement:

ALTER TABLE Persons DROP COLUMN DateOfBirth;

Here’s how the “Persons” table will appear now:

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