MYSQL – ALTER, DROP, RENAME, MODIFY

What is the ALTER Command in MySQL?

The ALTER command modifies an existing database, table, view, or other database object that needs to change during the life cycle of a database. It is the command that lets a schema evolve after it has already been created and filled with data.

Business requirements rarely stand still, and a database design has to move with them. Suppose the design for the Myflix database has been implemented and users are working with it, and only then does the team realise that vital information was left out. Nobody wants to lose the existing rows; they simply want to record the new information alongside them.

ALTER covers exactly that situation. It can change the data type of a field from a string to a number, rename a field, or add an entirely new column to a table, all while the data already stored in the table stays intact.

With the purpose of the command established, the next step is the syntax that carries it out.

MySQL ALTER Syntax

The basic syntax used to add a column to a table that already exists is shown below.

ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;

HERE

• “ALTER TABLE `table_name`” is the command that tells MySQL server to modify the table named `table_name`.
• “ADD COLUMN `column_name` `data_type`” is the command that tells MySQL server to add a new column named `column_name` with the data type `data_type`.

Suppose Myflix has introduced online billing and payments. To support it, a field for the credit card number has to be added to the members table. Before making any amendment, it is worth looking at the structure of the table as it stands. The script shown below does that.

SHOW COLUMNS FROM `members`;

💡 Note on int(11): the number in brackets is only a display width. MySQL 8.0.17 deprecated it for integer types, so a modern server reports this column simply as int. The storage size is unchanged.

The script shown below adds the new field to the members table.

ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25);

Executing this script against the myflixdb adds a column named credit_card_number to the members table with VARCHAR as the data type. Running the SHOW COLUMNS script again gives the following result.

The highlighted row confirms that credit_card_number has been added to the members table, and the data already held in the other columns is untouched by the addition.

Adding a column is only half the story. A schema also has to shed objects it no longer needs, which is the job of the DROP command.

What is the DROP Command in MySQL?

The DROP command deletes an object permanently. It is used to

1. Delete a database from the MySQL server.
2. Delete an object such as a table or a column from a database.

⚠️ Warning: DROP is not reversible. Neither the object nor the rows inside it can be recovered without a backup, so run a SELECT against the target and confirm the backup first.

Dropping a column

In the ALTER example above, a column named credit_card_number was added to the members table. Suppose the online billing feature is postponed and that column should be removed again. The following script does it.

ALTER TABLE `members` DROP COLUMN `credit_card_number`;

Executing the script drops the column credit_card_number from the members table. Listing the columns again confirms the result.

SHOW COLUMNS FROM `members`;

The credit card number is gone from the field list.

DROP TABLE

The syntax to drop an entire table from a database is as follows.

DROP TABLE `sample_table`;

Applied to a real object, the statement looks like this.

DROP TABLE `categories_archive`;

Executing it deletes the table named `categories_archive` and every row inside it from the database.

DROP DATABASE

The same keyword removes an entire schema. Adding IF EXISTS suppresses the error that MySQL raises when the target is already gone, which is useful inside scripts that run more than once.

DROP DATABASE IF EXISTS `myflixdb_backup`;

Removing an object is final. When the object itself should survive and only its name is wrong, the RENAME command is the correct tool.

What is the RENAME Command in MySQL?

The RENAME command changes the name of an existing database object, such as a table or a column, to a new name. Renaming a table does not cause it to lose any of the data contained within it.

The command has the following basic syntax.

RENAME TABLE `current_table_name` TO `new_table_name`;

To rename the movierentals table to movie_rentals, the script below is used.

RENAME TABLE `movierentals` TO `movie_rentals`;

Executing it renames the table `movierentals` to `movie_rentals`. Because the rename is only a change of label, the same statement reversed restores the original name.

RENAME TABLE `movie_rentals` TO `movierentals`;

RENAME TABLE handles the name of a table. Renaming a column, and changing what it holds, is the work of the CHANGE keyword.

CHANGE Keyword

The CHANGE keyword allows you to

1. Change the name of a column
2. Change the column data type
3. Change the column constraints

Consider an example. The full_names field in the members table is of the varchar data type with a width of 350, as the script below confirms.

SHOW COLUMNS FROM `members`;

Suppose the requirement is to

1. Change the field name from “full_names” to “fullname”
2. Change it to the char data type with a width of 250
3. Add a NOT NULL constraint

All three edits are carried out by a single CHANGE statement.

ALTER TABLE `members` CHANGE COLUMN `full_names` `fullname` char(250) NOT NULL;

Executing it against myflixdb and then running the SHOW COLUMNS script gives the following result.

The column is now called fullname, holds char(250), and rejects NULL values.

MODIFY Keyword

The MODIFY keyword allows you to

1. Modify the column data type
2. Modify the column constraints

In the CHANGE example above, the field name had to be supplied twice, even though the rename was only part of the goal. Omitting the field name from a CHANGE statement generates an error. When only the data type and the constraints need adjusting and the name should stay as it is, MODIFY does the job with one mention of the column.

The script below changes the width of the “fullname” field from 250 to 50.

ALTER TABLE `members` MODIFY `fullname` char(50) NOT NULL;

Executing it against myflixdb and then running the SHOW COLUMNS script gives the result shown below.

⚠️ Warning: narrowing a column truncates data that no longer fits. Any name longer than 50 characters is cut short by this statement, so check the longest existing value with a SELECT query before shrinking a field.

CHANGE vs MODIFY vs RENAME COLUMN

The three keywords overlap, and choosing the wrong one is the most common source of errors in this area. The table below sets them side by side.

MySQL 8.0 added a fourth option for the simplest case, a rename with no redefinition at all.

ALTER TABLE `members` RENAME COLUMN `fullname` TO `full_name`;

Type and name are settled. The remaining question is where a new column sits in the table, which the AFTER keyword controls.

AFTER Keyword

By default, a column added with ALTER TABLE lands at the end of the table. To place it at a specific position instead, the ALTER command is combined with the AFTER keyword.

The script below adds “date_of_registration” directly after date_of_birth in the members table.

ALTER TABLE `members` ADD `date_of_registration` date NULL AFTER `date_of_birth`;

Executing it against myflixdb and then running the SHOW COLUMNS script gives the result shown below.

The highlighted row is the column added after date_of_birth. The keyword FIRST works the same way and moves the new column to the very front of the table.

FAQs