SQL ALTER TABLE

You learnt in a previous lesson how to create a table. Once you created it, it is possible to change its properties. You do this using “alter table”.

Adding a column

To add a column called “month” of type integer in the table “revenues”, we use the following syntax:

ALTER TABLE revenues
ADD month int

If we want the column “month” to show just on the right hand side of the column “year”, the syntax is the following:

ALTER TABLE revenues
ADD month int AFTER year

If we do not indicate where to put the new column, it will be the last column in the table. As a result, it will appear completely at the right hand side of the results when using “select *” in the select statement.

Changing a column name

To change the name of the column “year” to “revenue_year” in the table “revenues” (assuming that the column is of type integer:

ALTER TABLE revenues
CHANGE year revenue_year int

Note that you have to indicate the column type again, even if you want only the change the column name.

Changing the properties of a column

Example: we want the column city to be of type varchar(70) instead of varchar(60). We also want to indicate that this column should not accept null values:

ALTER TABLE revenues
CHANGE city city VARCHAR(70) NOT NULL

Note that city appears twice. This is because with the change keyword, we can also change the column name at the same time as changing the properties. In our example, we do not want to change the column name. As a result, the old column name (city) and the new column name (city) are the same.

Deleting a column

To delete the column “revenu_year” from the “revenues” table:

ALTER TABLE revenues
DROP revenu_year

Carefull: if there are some rows in the table, it will not only delete the column. It will also delete the information inside the column for all rows.

Renaming the table

To change the name of the table “revenues” to “sales_revenue”:

ALTER TABLE revenues
RENAME TO sales_revenue

If you want to test your understanding of this article, take the quiz about table modification wih SQL.
You can also read about database administration with SQL.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: September 20, 2014