Insert Default Value in MySQL ENUM Data Type

When designing a database, setting default values for certain fields will improve data integrity. In MySQL, ENUM data type allows you to specify predefined values for a column such as status (complete or incomplete). This article helps you to set a default value for ENUM columns, to ensure that even if the data is missing, a suitable value is automatically assigned.

Inserting a default value with ENUM data type

We can do this with the help of the DEFAULT attribute of the ENUM data type. The DEFAULT attribute causes an ENUM data type to have a default value when a value is not specified. In other words, we can say that the INSERT statement does not have to include a value for this field because if it does not include then the value following DEFAULT will be inserted. Functions are not allowed in the DEFAULT expression. For ENUM data type the DEFAULT values include NULL and empty string(?').

Example 1

In this example, we will set a specific default value.

Let's create a table with ENUM data type which sets a default value as 'Fail'. The query is as follows:

CREATE TABLE student (
  Roll_no INT,
  Name VARCHAR(20),
  result ENUM('Pass', 'Fail') DEFAULT 'Fail'
);

Let us add the records to the student table using INSERT command.

INSERT INTO student(Rollno, Name) 
Values(25, 'Raman');

We have not inserted any value in the ?result' column hence it will pick the word following DEFAULT as the value. In our case, the default value, ?fail' would be inserted. Let us check by fetching the data using the SELECT command.

SELECT * FROM student;

Following is the output of the above code ?

Example 2

In this example, we will use 'NULL' as the default value. You can set NULL as the default value for an ENUM column until the user explicitly provides it.

Let us first create a table named course_enrollment with the Status column as ENUM data type and declare the default value as NULL.

CREATE TABLE course_enrollment (
   CourseId INT,
   StudentName VARCHAR(20),
   Status ENUM('Enrolled', 'Completed', 'Dropped') DEFAULT NULL
);

INSERT INTO course_enrollment (CourseId, StudentName)
VALUES (101, 'Alice');

SELECT * FROM course_enrollment;

Following is the output of the above query ?

From the above output, we can tell that as we didn't insert any value in the status column it was provided as NULL. Even if you do not specify the default value while creating a table, by default it stores NULL or empty string('').