MariaDB DEFAULT() Explained

In MariaDB, DEFAULT() is a built-in function that returns the default value for a table column.

If the column has no default value and it is nullable, then NULL is returned. If the column has no default value and it is not nullable, an error is returned.

Syntax

The syntax goes like this:

DEFAULT(col_name)

Where col_name is the name of the column for which to return the default value.

It can also be used without the parentheses and argument in certain cases:

DEFAULT

This syntax can be used to explicitly set a column to its default value.

Example

Here’s an example to demonstrate how the function works.

Suppose we create a table like this:

CREATE TABLE guest_meals (
  guest_id INT NOT NULL,
  meal VARCHAR(255) DEFAULT 'Salad',
  special_request VARCHAR(255),
  PRIMARY KEY (guest_id)
);

Here, we set a default value for the meal column. Specifically, we used DEFAULT 'Salad' to set the default meal to Salad.

This uses the DEFAULT clause of the CREATE TABLE statement. While related to our purpose here (it sets the default value for a column), it’s not to be confused with the DEFAULT() function that this article is about.

Now let’s insert some rows:

INSERT INTO guest_meals (guest_id) VALUES (1);
INSERT INTO guest_meals (guest_id, meal) VALUES (2, 'Fish');
INSERT INTO guest_meals (guest_id, meal) VALUES (3, 'Burrito');
INSERT INTO guest_meals (guest_id, meal, special_request) VALUES (4, 'Pasta', 'Hot');
INSERT INTO guest_meals (guest_id, special_request) VALUES (5, 'Vegan');

Some of these entries explicitly state which meal the guest wants while others don’t. The ones that don’t will just use the default meal (Salad).

So now let’s select those rows from the table. We will include the DEFAULT() function in our selection:

SELECT 
    guest_id,
    DEFAULT(meal),
    meal,
    DEFAULT(special_request),
    special_request
FROM guest_meals;

Result:

+----------+---------------+---------+--------------------------+-----------------+
| guest_id | DEFAULT(meal) | meal    | DEFAULT(special_request) | special_request |
+----------+---------------+---------+--------------------------+-----------------+
|        1 | Salad         | Salad   | NULL                     | NULL            |
|        2 | Salad         | Fish    | NULL                     | NULL            |
|        3 | Salad         | Burrito | NULL                     | NULL            |
|        4 | Salad         | Pasta   | NULL                     | Hot             |
|        5 | Salad         | Salad   | NULL                     | Vegan           |
+----------+---------------+---------+--------------------------+-----------------+

The DEFAULT(meal) column returns the default values, and the meal column returns the actual values that were inserted.

Similarly, the DEFAULT(special_request) column returns the default values for that column, and the special_request column returns the actual values that were inserted.

Regarding the special_request column, we didn’t actually set a default value for this column and so the result is NULL. Note that if the column was not nullable, then we’d get an error (see below). But the column is nullable, and so the default value of NULL returned.

Explicitly Inserting the Default Value

You can use the DEFAULT keyword without parentheses to explicitly insert the default value into a column.

Here’s an example:

INSERT INTO guest_meals (guest_id, meal, special_request) 
VALUES (6, DEFAULT, DEFAULT);

Now let’s select that row:

SELECT 
    guest_id,
    DEFAULT(meal),
    meal,
    DEFAULT(special_request),
    special_request
FROM guest_meals
WHERE guest_id = 6;

Result:

+----------+---------------+-------+--------------------------+-----------------+
| guest_id | DEFAULT(meal) | meal  | DEFAULT(special_request) | special_request |
+----------+---------------+-------+--------------------------+-----------------+
|        6 | Salad         | Salad | NULL                     | NULL            |
+----------+---------------+-------+--------------------------+-----------------+

In both cases, it inserted the default value for the respective column. For the meal column, this was Salad. For the special_request column, this was NULL.

Non-Nullable Columns

As mentioned, trying to get the default value from a non-nullable column that doesn’t have a default value results in an error.

Example:

SELECT DEFAULT(guest_id)
FROM guest_meals;

Result:

ERROR 1364 (HY000): Field 'guest_id' doesn't have a default value

When we created the table, we didn’t provide a default value for the guest_id column, and we also set it to NOT NULL. Because of this, we get an error when trying to return its default value.

Integer Columns using `AUTO_INCREMENT`

For integer columns using AUTO_INCREMENT, the return value is 0.

As an example, let’s create a table with an AUTO_INCREMENT column:

CREATE TABLE guest (
  guest_id INT NOT NULL AUTO_INCREMENT,
  guest_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (guest_id)
);

The guest_id column uses an INT (integer) data type, and it’s set to AUTO_INCREMENT.

Now insert some rows:

INSERT INTO guest (guest_name) VALUES ('Homer');
INSERT INTO guest (guest_name) VALUES ('Bart');
INSERT INTO guest (guest_name) VALUES ('Marge');

Now, let’s return the default value of the guest_id column:

SELECT 
    DEFAULT(guest_id),
    guest_id
FROM guest;

Result:

+-------------------+----------+
| DEFAULT(guest_id) | guest_id |
+-------------------+----------+
|                 0 |        1 |
|                 0 |        2 |
|                 0 |        3 |
+-------------------+----------+

The DEFAULT(guest_id) column shows that the default value is 0 for all rows. The guest_id column shows the actual value that was inserted (which is was generated by the AUTO_INCREMENT).

Syntax

Example

Explicitly Inserting the Default Value

Non-Nullable Columns

Integer Columns using AUTO_INCREMENT

Integer Columns using `AUTO_INCREMENT`