Wednesday, 15 February 2012

SQL: the alter table statement


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.

We can add a new column with a simple statement:
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.

In order to change the data type of table, we can use:
ALTER COLUMN columnName dataType
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:
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.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.