Enum data type in MySQL

While investigating features of MySQL database engine, I came across an interesting concept: the enum data type.

The enum data type allows for defining static data ranges that can be used as values of certain attributes. These ranges typically are quite small - for example marital status consists of 'Married', 'Single', 'Widowed', 'Divorced', 'N/A' .

These five values can be encoded as an enum data type column. For example, we can define a table in the following way.

create table Person 
(
	FirstName varchar(50) not null,
	SurName varchar(50) not null,
	MaritalStatus enum ('Married', 'Single', 'Widowed', 'Divorced', 'N/A') not null
);

Please note this is a very simplified code. Enum in MySQL has a certain feature - the values in rows inserted into the table are stored as indexes to the values defined in the CREATE TABLE statement. This means there are potentially huge savings in storage space, if the character values are long and there is many records.

There is a lot of documentation about the enum data type, and I won't be repeating it here. One of the better articles is available on the official documentation portal: link.

What can be benefits of an enum column? In SQL Server for example, in order to achieve similar effect, you have two choices.

First choice - Use a check constraint that will list all allowed values.

create table dbo.Person 
(
	FirstName varchar(50) not null,
	SurName varchar(50) not null,
	MaritalStatus varchar(20) not null 
		check (MaritalStatus in ('Married', 'Single', 'Widowed', 'Divorced', 'N/A'))
)

Please note, that values in this column are stored in their varchar format. This means that the size of the table will grow much more than in case of an integer column. You also cannot insert a value to the column MaritalStatus that does not match one of the values in the CHECK constraint. With enum on MySQL, it is possible.

Second choice - Use a lookup table and a foreign key constraint

create table dbo.MaritalStatusList 
(
	Id int not null primary key,
	MaritalStatus varchar(50) not null
);

create table dbo.Person 
(
	FirstName varchar(50) not null,
	SurName varchar(50) not null,
	MaritalStatus int not null 
		foreign key references dbo.MaritalStatusList(Id)
);

The second approach provides a normalized approach to the database design, yet, in some cases it can be problematic. Your CRUD operations on the Person table have to operate on the foreign key values. You can alleviate this with stored procedures, clever ORMs, triggers, CRUD operations on a view, but the fact stays the same - the enum data type can be much easier to manage.