Error 1067 – SQLSTATE: 42000 (ER_INVALID_DEFAULT) “Invalid default value for ‘%s'” in MySQL

by

Error 1067 in MySQL is triggered when you define a table with a column that has an invalid default value. This can occur due to several reasons, such as using a value that does not match the column’s data type, or when the default value does not adhere to MySQL’s rules for the particular data type, especially for DATE, DATETIME, and TIMESTAMP types. Understanding the cause of this error is the first step in resolving it.

Diagnosing the Error

To diagnose Error 1067, inspect the column definitions in your table creation or alteration statements. Pay special attention to the DEFAULT constraints and check if:

  1. The default values match the data type of the columns.
  2. For DATE or DATETIME columns, the default value is in ‘YYYY-MM-DD’ or ‘YYYY-MM-DD HH:MM:SS’ format, respectively.
  3. For TIMESTAMP columns, make sure that the default value is either a valid timestamp or CURRENT_TIMESTAMP.
  4. The default value does not violate any other constraints, such as NOT NULL without an explicit default value.

Fixing the Error

Here are several examples and sample code to help you fix Error 1067:

Example 1: Incorrect DATE Format

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE DEFAULT '2024-02-30'
);

The above statement will cause Error 1067 because ‘2024-02-30’ is not a valid date. The corrected statement is:

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE DEFAULT '2024-02-28'
);

Example 2: Invalid DATETIME Default

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_datetime DATETIME DEFAULT 'now()'
);

MySQL expects a valid datetime string or a function like CURRENT_TIMESTAMP for the default value:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_datetime DATETIME DEFAULT CURRENT_TIMESTAMP
);

Example 3: TIMESTAMP Without Default

CREATE TABLE user_logins (
    id INT PRIMARY KEY,
    login_time TIMESTAMP NOT NULL
);

A NOT NULL TIMESTAMP column requires an explicit default value:

CREATE TABLE user_logins (
    id INT PRIMARY KEY,
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Example 4: ENUM with Invalid Default

CREATE TABLE shirts (
    id INT PRIMARY KEY,
    size ENUM('small', 'medium', 'large') DEFAULT 'extra large'
);

The default value must be one of the listed options in the ENUM:

CREATE TABLE shirts (
    id INT PRIMARY KEY,
    size ENUM('small', 'medium', 'large') DEFAULT 'medium'
);

Conclusion

When faced with Error 1067, carefully review your column definitions and ensure that all default values are valid for the data types and constraints you have set. Properly defining default values that comply with MySQL’s rules will help you avoid this error and ensure that your database tables are created or altered successfully.

Leave a Comment