If you have an existing column in MySQL that you want to add a DEFAULT clause to, the following example may help.

To explicitly add a DEFAULT clause to an existing column in MySQL, we can use the ALTER TABLE statement along with the MODIFY clause.

We can also add a DEFAULT clause implicitly by defining a column as nullable. When we do this, MySQL implicitly adds its own DEFAULT clause.

Example

Suppose we create the following table:

CREATE TABLE guest_meals (
    guest_id int NOT NULL,
    meal varchar(255) NOT NULL,
    special_request varchar(255)
);

We can add DEFAULT clauses to this table either explicitly or implicitly.

Explicitly Add a DEFAULT Clause

Here’s an example of code that explicitly adds a DEFAULT clause to the meal column:

ALTER TABLE guest_meals 
MODIFY meal varchar(255) NOT NULL DEFAULT 'Salad';

Here, I redefined the column as it originally was, but I also included a DEFAULT clause. This allows me to specify a default value to use if one isn’t provided when a new row is inserted.

In this example, if a guest doesn’t choose a meal, then their default meal will be salad.

Implicitly Add a DEFAULT Clause

We can also add a DEFAULT clause implicitly on certain columns.

The way it works is, if we don’t explicitly add a DEFAULT clause, and the column is nullable, MySQL adds its own DEFAULT clause to that column.

Lets see what MySQL has done with our table so far:

SHOW CREATE TABLE guest_meals;

Result:

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                             |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| guest_meals | CREATE TABLE `guest_meals` (
  `guest_id` int NOT NULL,
  `meal` varchar(255) NOT NULL DEFAULT 'Salad',
  `special_request` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

First of all, we can see the DEFAULT column that we added to the meal column in the previous example.

But we can also see that MySQL has already added DEFAULT NULL to the special_request column, even though we didn’t include that in our table definition. MySQL did this because we set the column as nullable (i.e. we didn’t make it NOT NULL).

So we’ve already implicitly created a DEFAULT clause against the table without even knowing it. But now let’s do it again – to the existing table. Let’s do it to our meals column.

To implicitly add a DEFAULT clause to our meals column, we’ll need to remove the existing DEFAULT constraint and make the column nullable:

ALTER TABLE guest_meals 
MODIFY meal varchar(255) NULL;

Here, I explicitly stated NULL but we could have just omitted this keyword to get the same outcome.

Now let’s take a look at the table definition again:

SHOW CREATE TABLE guest_meals;

Result:

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| guest_meals | CREATE TABLE `guest_meals` (
  `guest_id` int NOT NULL,
  `meal` varchar(255) DEFAULT NULL,
  `special_request` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQL has implicitly applied an explicit DEFAULT NULL against the column.

So we could say that we just implicitly added a DEFAULT clause to our column.