Adding new enum column to an 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.

Creating 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 ); 

Describing the table

Check the description of the table using DESC command:

 DESC DemoTable; 

Following is the output of the above query ?

Field	Type	Null	Key	Default	Extra
StudentId	int	NO	PRI	NULL	auto_increment
StudentName	varchar(200)	YES		NULL
StudentAge	int	YES		NULL

Adding ENUM column

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; 

Verification

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:

Field	Type	Null	Key	Default	Extra
StudentId	int(11)	NO	PRI	NULL	auto_increment
StudentName	varchar(200)	YES		NULL
StudentAge	int(11)	YES		NULL
StudentGender	enum('Male','Female')	NO		NULL

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