SQL: enum type

If you have a requirement, where user needs to select a value from fixed set of values, enum is your buddy.

 

How to define enum?

ENUM column can be defined at the time of table creation time.

 

CREATE TABLE table_name (

    ...

    column_name ENUM ('value1','value2','value3'),

    ...

);

 

Example

CREATE TABLE person (

         id int AUTO_INCREMENT,

         name varchar(20),

         gender ENUM ('M','F'),

         PRIMARY KEY(id)

);

 

mysql> CREATE TABLE person (
    -> id int AUTO_INCREMENT,
    -> name varchar(20),
    -> gender ENUM ('M','F'),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> desc person;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int           | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)   | YES  |     | NULL    |                |
| gender | enum('M','F') | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

 

Insert some records to person table.

INSERT INTO person(name, gender) VALUES ('gopi', 'M');

INSERT INTO person(name, gender) VALUES ('joel', 'M');

INSERT INTO person(name, gender) VALUES ('harika', 'F');

mysql> INSERT INTO person(name, gender) VALUES ('gopi', 'M');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person(name, gender) VALUES ('joel', 'M');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person(name, gender) VALUES ('harika', 'F');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM person;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | gopi   | M      |
|  2 | joel   | M      |
|  3 | harika | F      |
+----+--------+--------+
3 rows in set (0.00 sec)

 Try to insert some record with invalid data for gender column, you will get an error from MySQL server.

 

mysql> INSERT INTO person(name, gender) VALUES ('bddaa', 'X');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> 
mysql> INSERT INTO person(name, gender) VALUES ('harika', 'a');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

 Since, I didn’t set NOT NULL constraint on gender, column, it can take NULL values.

 

mysql> INSERT INTO person(name) VALUES ('harika');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM person;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | gopi   | M      |
|  2 | joel   | M      |
|  3 | harika | F      |
|  4 | harika | NULL   |
+----+--------+--------+
4 rows in set (0.00 sec)

 

To not accept NULL values, you can add ‘NOT NULL’ constraint on the column gender. You can do this at the time of table creation.

 

CREATE TABLE person (

    id int AUTO_INCREMENT,

    name varchar(20),

    gender ENUM ('M','F') NOT NULL,

    PRIMARY KEY(id)

);

 

                  (or)

Using alter statement, you can apply NOT NULL constraint on existing column.

ALTER TABLE person MODIFY gender ENUM('M', 'F') NOT NULL;

mysql> ALTER TABLE person MODIFY gender ENUM('M', 'F') NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

 

 

 

 

 

 

Previous                                                    Next                                                    Home