This error occurs when you attempt to INSERT or UPDATE a row in a MySQL table without providing a value for a NOT NULL column that has no default value. It is triggered only when MySQL is running in Strict mode, which enforces data integrity rules.
MySQL Error 1364 (ER_NO_DEFAULT_FOR_FIELD) is a strict mode validation error that prevents data from being inserted into NOT NULL columns without explicit values. When a column is defined as NOT NULL, MySQL requires either a value to be provided during INSERT/UPDATE or a DEFAULT clause to be set at the table level. In Strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES), MySQL converts this validation warning into a fatal error. This protects against accidental data loss and helps maintain referential integrity. Without this error, databases could end up with incomplete or NULL data in columns that should never be empty.
The error message will specify the exact column name that is causing the issue. For example:
ERROR 1364 (HY000): Field 'user_id' doesn't have a default valueExamine your INSERT statement and check if you are omitting this column. Most commonly this happens with auto-increment ID columns or status fields.
The simplest solution is to include a value for every NOT NULL column in your INSERT statement:
-- Before (causes error)
INSERT INTO users (name, email) VALUES ('John', '[email protected]');
-- After (works correctly)
INSERT INTO users (id, name, email, status) VALUES (1, 'John', '[email protected]', 'active');If the column is an auto-increment field, you can omit it if it is truly AUTO_INCREMENT. Otherwise, provide the value explicitly.
For columns that should have a sensible default, use ALTER TABLE to add a DEFAULT clause:
ALTER TABLE users MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';ALTER TABLE products MODIFY COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;This allows INSERT statements to omit the column, and MySQL will use the default automatically. Only use defaults when a logical fallback value exists.
If the offending column is an ID field, ensure it is properly configured with AUTO_INCREMENT:
ALTER TABLE users MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;Once AUTO_INCREMENT is set, you can omit the id column from INSERT statements and MySQL will generate unique values automatically:
INSERT INTO users (name, email) VALUES ('Jane', '[email protected]');
-- id will be auto-generated (1, 2, 3, etc.)If the column does not truly require a value in all cases, change it to allow NULL:
ALTER TABLE orders MODIFY COLUMN notes VARCHAR(500) NULL;Then INSERT statements can omit this column or explicitly set it to NULL:
INSERT INTO orders (id, customer_id) VALUES (1, 100);
-- notes will be NULLUse this approach carefully—only when NULL is semantically correct for that field.
MySQL Strict Mode: Error 1364 only occurs when MySQL is running in Strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES). In permissive mode, the same INSERT would succeed but MySQL would insert the column's implicit default (0 for numbers, empty string for text). If you must disable Strict mode, edit your MySQL configuration file (my.cnf or my.ini) and set sql_mode="" under the [mysqld] section. However, this is discouraged as it weakens data integrity checks.
Framework Implications: Many frameworks (Laravel, Django, Rails) handle auto-increment fields implicitly. If you are using an ORM, ensure you are not explicitly providing NULL or omitting required columns. Consult your framework's documentation for the correct way to define models.
Schema Design Best Practice: Always define NOT NULL columns with either a sensible DEFAULT or use AUTO_INCREMENT for ID columns. This prevents this error class entirely. When adding NOT NULL columns to existing tables, provide a DEFAULT value or a backfill migration to populate existing rows.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL