Invalid default value for column

What this means

Error 1067 (SQLSTATE 42000) is raised during CREATE TABLE or ALTER TABLE when a column's DEFAULT value is incompatible with its data type, violates a constraint, or is a non-deterministic function that is not permitted as a default in that context.

Why it happens

1. 1DEFAULT value literal does not match the column's data type (e.g., DEFAULT 'abc' on an INT column)
2. 2DEFAULT NOW() or DEFAULT CURRENT_TIMESTAMP used on a DATE column in older MySQL/MariaDB versions
3. 3Strict SQL mode rejects values that would otherwise be silently coerced
4. 4DATETIME column has DEFAULT '0000-00-00 00:00:00' with NO_ZERO_DATE mode active

How to reproduce

Creating a table with an invalid default value.

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_date DATE DEFAULT NOW()  -- NOW() is not valid for DATE default
);

Fix 1: Use a valid literal default for DATE columns

When the column type is DATE or another type that does not accept NOW().

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_date DATE DEFAULT (CURRENT_DATE)  -- MariaDB 10.2.1+ expression default
);
-- Or simply omit the default and set it in application logic

Fix 2: Replace zero-date default

When '0000-00-00' is rejected by NO_ZERO_DATE mode.

-- Change default to NULL (if column is nullable):
ALTER TABLE events MODIFY event_date DATE NULL DEFAULT NULL;

What not to do

Version notes

Sources

Confidence assessment

See also