ALTER TABLE: Examples with MySQL – Beginner Series.

In this second part of the MySQL Beginner Series Basics, I will explore various uses of the ALTER TABLE syntax.  Part 1 covered the CREATE command, establishing a database, table, and user. This blog post will build from there, so be sure to visit that post to get up to speed.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Photo by Luca Bravo on Unsplash

Objectives.

• Use ALTER TABLE ADD COLUMN syntax to add in a new column to an existing table.
• Use the CHANGE clause to change the name of existing columns in the target table.
• Use the MODIFY clause to change a columns’ definition, but not its name.

Adding in a new column.

Below is a description of the target ‘friends’ table I will be working with.

This friends table would not serve us well at all if we did not remember a friend’s birthday. How about we add that column to the table?

Having limited privileges, this_user (created in Part 1), needs the ALTER command privilege in order to change or modify the friends table.

That privilege is granted with the below command:

Now this_user canALTER the friends table and add in that birthday column.

With the above command, I have added the f_bday column and defined it to have the DATE data type.
Let’s confirm by re-checking the table description:

Sweet. Now we will not forget a single birthday!

That column name is… meh

Suppose you want to change the first_name, last_name, and cell_phone column names prefixing them with an f to denote a ‘friend’ column.

All 4 column names are changed and prefixed with f by using the CHANGE clause within the ALTER TABLE command:

Shown above, the basic syntax for renaming a column with the CHANGE clause is:

Noteworthy: Notice the column definition is still required for changing only the column name, when using the CHANGE clause. To keep the original definition, just specify it in the command syntax while renaming.

And checking out the column names.

Boom! Now I know for certain what those column names represent.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

This data type needs to be reconfigured…

Finally for demonstration purposes, suppose you want to change the data type of the f_cell_phone column from VARCHAR(15) to CHAR(12)?
Since most phone numbers, to my knowledge (In the U.S. at least), have 12 total values, including two dashes (–), the CHAR data type would be a good fit for this column, as it will be fixed length.
Storing the area code, 7-digit number, and both dashes, it likely will maintain this consistency
In this instance, the ALTER TABLE command along with the MODIFY clause will accomplish this.

This syntax is faily simple,

                                                                        TIP:
You can use the CHANGE clause in lieu of the MODIFY clause for this purpose as well.

A final description of the friends table, showing all the changes made this session.

Key Takeaways.

• CHANGE clause:
  • Can rename or re-define a column or both.
  • Using FIRST or AFTER can reorder columns. (Not shown in this post)
• MODIFY clause:
  • Cannot change a column’s name, but can change its definition.
  • Can also reorder columns using the FIRST and AFTER commands. (Not shown in this post)

Closing.

Pairing the ALTER TABLE command, with the CHANGE and MODIFY clauses, provides the ability to change table column names and definitions as your programming or database specifications change and dictate.
Join me in the next part of the series where I will INSERT data into our table.

Feel free to visit the official MySQL 5.7 Online Manual for further study or questions.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.