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.


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.

mysql> DESC friends;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(30) | NO | | NULL | |
| last_name | varchar(30) | NO | | NULL | |
| cell_phone | varchar(15) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.05 sec)

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:

mysql> GRANT ALTER ON friends.* TO 'this_user'@'localhost';
Query OK, 0 rows affected (0.07 sec)

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

mysql> ALTER TABLE friends ADD COLUMN f_bday DATE;
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0

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:

mysql> DESC friends;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(30) | NO | | NULL | |
| last_name | varchar(30) | NO | | NULL | |
| cell_phone | varchar(15) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

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:

mysql> ALTER TABLE friends CHANGE first_name f_first_name VARCHAR(30);
Query OK, 0 rows affected (0.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE friends CHANGE last_name f_last_name VARCHAR(30);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE friends CHANGE cell_phone f_cell_phone VARCHAR(15);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE friends CHANGE age f_age INTEGER(11);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

CHANGE current_column_name new_column_name data_type;

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.

mysql> DESC friends;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| f_first_name | varchar(30) | YES | | NULL | |
| f_last_name | varchar(30) | YES | | NULL | |
| f_cell_phone | varchar(15) | YES | | NULL | |
| f_age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

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.

mysql> ALTER TABLE friends MODIFY f_cell_phone CHAR(12);
Query OK, 1 row affected (1.76 sec)
Records: 1 Duplicates: 0 Warnings: 0

This syntax is faily simple,

MODIFY column_name new_data_type;

                                                                        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.

mysql> DESC friends;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| f_first_name | varchar(30) | YES | | NULL | |
| f_last_name | varchar(30) | YES | | NULL | |
| f_cell_phone | char(12) | YES | | NULL | |
| f_age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

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.

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.


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.


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). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “ALTER TABLE: Examples with MySQL – Beginner Series.

Leave a Reply