ENUM and SET types in MySQL - Step-by-Step Execution

Concept Flow - ENUM and SET types

Define ENUM or SET column with allowed values

↓

Insert or Update row with value(s)

↓

Check if value(s) match allowed list

↓

Store value(s)

↓

Retrieve stored value(s)

ENUM and SET columns store predefined values. Data must match allowed values or insertion fails.

Execution Sample

MySQL

CREATE TABLE shirts (
  size ENUM('small', 'medium', 'large'),
  colors SET('red', 'green', 'blue')
);

INSERT INTO shirts VALUES ('medium', 'red,blue');

Create a table with ENUM and SET columns, then insert a row with valid values.

Execution Table

Step	Action	Input Value	Check Against Allowed Values	Result	Stored Value
1	Create table	size ENUM('small','medium','large'), colors SET('red','green','blue')	N/A	Table created	N/A
2	Insert row	size='medium', colors='red,blue'	Check 'medium' in ENUM list	Valid	size='medium'
3	Insert row	colors='red,blue'	Check 'red' and 'blue' in SET list	Valid	colors='red,blue'
4	Retrieve row	N/A	N/A	Success	size='medium', colors='red,blue'

💡 Insertion stops after validating all values are allowed.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
size	NULL	'medium'	'medium'	'medium'
colors	NULL	'red,blue'	'red,blue'	'red,blue'

Key Moments - 2 Insights

Why can't I insert a value not listed in ENUM or SET?

Because ENUM and SET only accept predefined values. See execution_table rows 2 and 3 where values are checked and must be valid.

How does SET store multiple values?

SET stores multiple allowed values separated by commas, as shown in execution_table row 3 where 'red,blue' is valid.

Visual Quiz - 3 Questions

Look at the execution_table, what is the stored value of 'size' after step 2?

A 'small'

B 'medium'

C NULL

D 'large'

💡 Hint

Check the 'Stored Value' column in row 2 of execution_table.

At which step does the system check if 'red' and 'blue' are allowed SET values?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Check Against Allowed Values' column for 'red' and 'blue' in execution_table.

If you try to insert 'extra-large' into size ENUM, what happens?

A It stores 'extra-large' anyway

B It stores NULL silently

C It causes an error and insertion fails

D It converts to 'large'

💡 Hint

Refer to key_moments about invalid values and execution_table exit_note.

Concept Snapshot

ENUM and SET types store predefined values.
ENUM allows one value from a list.
SET allows multiple values from a list.
Invalid values cause insertion errors.
Use ENUM for single choice, SET for multiple choices.

Full Transcript

ENUM and SET are special column types in MySQL that let you store only specific allowed values. ENUM stores one value from a list, like sizes small, medium, or large. SET stores zero or more values from a list, like colors red, green, and blue. When you insert data, MySQL checks if the values match the allowed list. If they do, it stores them; if not, it gives an error. For example, inserting 'medium' into an ENUM size column works because 'medium' is allowed. Inserting 'red,blue' into a SET colors column works because both 'red' and 'blue' are allowed. Trying to insert a value not in the list, like 'extra-large' in size, causes an error and stops the insertion. This helps keep data clean and consistent.