SQL: the alter table statement

Tweet

In SQL we usually create tables, pre-deciding the columns and their characteristics, like default values and data types. It is possible to insert new columns and eventually alter them afterwards, using the alter table statement.
In the following post we are going to see what we can do with it.

With alter table, we can add, change the data type or delete a column inside a table.

Add

We can add a new column with a simple statement:

ALTER TABLE tableName
ADD columnName dataType

where tableName is the name of an existing table, columnName is the name of the new column and dataType is the data type of the new column.

Change

In order to change the data type of table, we can use:

ALTER TABLE tableName
ALTER COLUMN columnName dataType

Delete

The dropping of a column is again very simple, however it should be used with care, because it will obviously delete all the values in the column:

ALTER TABLE tableName
DROP COLUMN columnName

Use of alter table

There are many possible use of the alter table statement. For example, we might need to create a temporary column in a table, use it and then drop it. Or maybe, in some rare cases, columns might be added on users need, specifically in web applications, where a super user can manage the back end database through an option panel board.