1048ERRORTier 1 — Safe✅ HIGH confidence

Column cannot be null

Category: Constraint ViolationVersions: All MariaDB / MySQL versions

What this means

Error 1048 (SQLSTATE 23000) is raised when an INSERT or UPDATE attempts to set a NOT NULL column to NULL, and strict SQL mode is enabled. In non-strict mode, MariaDB may substitute the column's implicit default value and produce a warning instead of an error.

Why it happens

1Inserting a row without providing a value for a NOT NULL column that has no DEFAULT
2Explicitly passing NULL for a NOT NULL column
3Bulk loading data (LOAD DATA INFILE) where some rows have missing values for NOT NULL columns

How to reproduce

An INSERT omits a required NOT NULL column with no DEFAULT.

trigger — this will ERROR

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (customer_id) VALUES (42);  -- amount is NULL

ERROR 1048 (23000): Column 'amount' cannot be null

Fix 1: Provide a value for every NOT NULL column

Always.

fix

INSERT INTO orders (customer_id, amount) VALUES (42, 99.99);

Why this works

Explicitly providing all NOT NULL column values in the INSERT column list satisfies the constraint check before the row is written to the storage engine.

Fix 2: Add a DEFAULT value to the column

When a sensible default exists.

fix

ALTER TABLE orders MODIFY amount DECIMAL(10,2) NOT NULL DEFAULT 0.00;
INSERT INTO orders (customer_id) VALUES (42);  -- amount defaults to 0.00

Why this works

When a DEFAULT is defined and the column is omitted from the INSERT, the server substitutes the default value before constraint validation.

What not to do

✗

Disable strict mode to make the error a warning

Why it's wrong: In non-strict mode, MariaDB silently substitutes a zero/empty default, creating logically incorrect data (e.g., zero-amount orders) that is harder to detect.

Version notes

MariaDB 10.2+Strict mode (STRICT_TRANS_TABLES) is enabled by default. Changing sql_mode to non-strict will downgrade this error to a warning.

Sources

📚 Official docs: https://mariadb.com/kb/en/server-system-variables/#sql_mode

🔧 Source ref: MariaDB Server error code 1048 / ER_BAD_NULL_ERROR

📖 Further reading: MariaDB SQL mode

Confidence assessment

✅ HIGH confidence

Stable and well-documented. Strict mode default behaviour confirmed in MariaDB documentation.