MattCASmith

It’s been a while since I’ve written anything new for the series documenting my SQL learning, so this week I’m going to explain the various ways you can add, modify, and drop columns in a table using the ALTER TABLE statement.

Many SQL database management systems provide nice graphical interfaces that allow users to alter their SQL tables, but sometimes – either due do the lack of a GUI or its limitations – you can’t avoid getting your hands dirty and making changes to your columns yourself. I recently found myself in exactly that situation and was forced to learn how to use ALTER TABLE – here’s what it can do.

Adding a column to a SQL table

Adding columns is fairly straightforward. You simply need to specify the table to add to, the new column’s name, and the type of data you’re planning on storing there.

ALTER TABLE customer_list
ADD home_address varchar(500)

The trickiest part of adding a column is choosing the correct data type. In simple terms, the data can be a string, a number, or a timestamp – but there are a number of variations of each of these. In this case we’ve specified that the new column will hold strings with a maximum length of 500 characters.

Changing a column’s data type in a SQL table

Altering a column looks almost identical to adding a new column. Specify which column you’re editing and which table it sits in, and then tell the system what the new data type should be.

ALTER TABLE customer_list
ALTER COLUMN home_address varchar(600)

In the example above, we’ve changed our 500-character column to one that supports up to 600 characters, although we could just as easily have changed it to any other SQL data type.

Renaming a column in a SQL table

If we want to rename a column, we take pretty much the same approach as we did when we changed the data type, and specify both the current name and the new name we wish to apply.

ALTER TABLE customer_list
CHANGE home_address work_address

In this case, we’ve altered our home_address column and changed its name to work_address. All of the existing data in the column remains intact – it’s just the name that changes.

Dropping a column in a SQL table

Finally, if we want to delete the column we’ve added to our SQL table, we can use the notorious DROP command, which features in the classic xkcd web comic Exploits of a Mom.

ALTER TABLE customer_list
DROP COLUMN work_address

The command above will remove the work_address column from the table customer_list. Note that this deletes both the column and all the data it contains, so be careful!

Photo from Abdallah Maqboul on Pexels