SQL Alter table
SQL ALTER TABLE Statement
An existing table’s columns can be added, removed, or changed using the ALTER TABLE statement.
It is also possible to add and remove different constraints from an existing table using the ALTER TABLE statement.
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an “Email” column to the “Customers” table:
Example
---------- Example MUKAVU ---------
ALTER TABLE - DROP COLUMN
The following syntax can be used to remove a column from a table (keep in mind that some database systems prevent column deletions):
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the “Email” column from the “Customers” table:
Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - RENAME COLUMN
Use the following syntax to rename a column in a table:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
To rename a column in a table in SQL Server, use the following syntax:
SQL Server:
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';
ALTER TABLE - ALTER/MODIFY DATATYPE
Use the following syntax to modify a column’s data type in a table:
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
Look at 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 |
The “Persons” table now needs a column called “DateOfBirth” added to it.
We make use of the SQL query that follows:
ALTER TABLE Persons
ADD DateOfBirth date;
The new column “DateOfBirth” has a date type and will store a date, as you can see. What kind of data a column can include is determined by its data type. Visit our comprehensive Data Types reference to get a list of every data type that is supported by MS Access, MySQL, and SQL Server.
This is how the “Persons” table will appear now:
| 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 wish to modify the data type of the “DateOfBirth” column in the “Persons” table.
We make use of the SQL query that follows:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
It is evident that the “DateOfBirth” column has changed to a year type, displaying a year in either a two- or four-digit format.
DROP COLUMN Example
The “DateOfBirth” column in the “Persons” database should then be deleted.
We make use of the SQL query that follows:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
The “Persons” table will now look like this:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
