Add New ENUM Column to Existing MySQL Table

When updating a database, you may get a situation to add a new column to capture specific predefined values. For Example, if you are managing student data there will be a column gender that has fixed options like (Female or Male), in this case, an ENUM data type will be perfect.

ALTER Command 

To add a new enum column to an existing MySQL table, you can use the ALTER command.

The MySQL ALTER command is used to modify the existing structure of a table. It enables you to make several kinds of changes, such as adding, deleting, and changing columns in a table.

It's necessary for making structural updates to tables without recreating them, allowing flexibility for evolving database requirements. Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL.

Syntax

Following is the syntax to alter a table.

ALTER TABLE table_name 
ADD column_name 
ENUM('yourValue1','yourValue2'....N) NOT NULL;

Example

To add an ENUM column for the existing MySQL table.

To understand the above syntax first let us create table. The query is as follows:

CREATE TABLE DemoTable (
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentName varchar(200),
   StudentAge int
);

Check the description of the table using DESC command:

 DESC DemoTable;

Following is the output of the above query ?

Following is the query to add a new enum column to the existing table. We have set it for Student Gender:

ALTER TABLE DemoTable ADD StudentGender 
 ENUM('Male','Female') NOT NULL;

Let us check the description of table once again. We use DESC command.

 DESC DemoTable;

This will produce the following output and display the enum values as well for GENDER:

Look at the above output, the column StudentGender has data type ENUM.