SQL Standard

SQL Enum

MySQL supports the enum type. An enum is a unique string object in which the value is selected from a list of pre-defined values.

If you have ever used HTML, think of them as radio buttons with a set of values, and you can only select one value.


MySQL allows us to create an enum column using the syntax shown below:

  col ENUM ('value_1','value_2','value_3', ....),

The enum type takes three attributes:

  1. DEFAULT – sets the default value if the user does not select any value from a list of provided options.
  2. NULL – sets the default value to null.
  3. Not null – prevents the column from containing non-null values by forcing the user to select a value or use any default value.


A classic example of the enum type is storing clothes size. Let us show how to use the enum type by creating a table that stores clothes info.

An example is as shown:

    name TEXT,
    SIZE ENUM('x-small', 'small', 'medium', 'large', 'x-large')

The above query creates a table called shirts that store the shirt’s name and the size of the enum type. In addition, the enum contains possible shirt sizes.

NOTE: Although PostgreSQL supports enum type, it requires you to define your enum values as a specific type.

An example is as shown:

CREATE TYPE SIZE AS enum ('x-small', 'small', 'medium', 'large', 'x-large');

Once you have the table declared with enum type, you can insert records as shown:

INSERT INTO shirts(name, SIZE) VALUES ('polo shirt', 'medium'), ('t-shirt', 'x-small');

Once inserted, you can select specific rows using the where clause as shown:

SELECT name, SIZE FROM shirts WHERE SIZE = 'x-small';

Enum Index

Each value in an enum column is assigned an index starting from index 1. You can use this index to select, delete, or update a specific record.

For example, to select the record where the enum value is x-small using its index, we can do:


NOTE: You can also use an enum type to sort the rows.


This article gives you practical usage of the enum type in SQL. Check the documentation to explore more.



About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list