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