Adding new enum column to an existing MySQL table?

MySQLMySQLi Database

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

ALTER TABLE yourTableName ADD yourColumnName ENUM('yourValue1','yourValue2’....N) NOT NULL;

Let us first create a table:

mysql> create table DemoTable
(
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentName varchar(200),
   StudentAge int
);
Query OK, 0 rows affected (0.62 sec)

Check the description of table using DESC command:

mysql> DESC DemoTable;

This will produce the following output:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| StudentId   | int(11)      | NO   | PRI | NULL    | auto_increment |
| StudentName | varchar(200) | YES  |     | NULL    |                |
| StudentAge  | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

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

mysql> ALTER TABLE DemoTable ADD StudentGender ENUM('Male','Female') NOT NULL;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the description of table once again:

mysql> 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    |                |
+---------------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

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

raja
Published on 29-Apr-2019 08:47:01
Advertisements