3614
MySQLERRORCommonDDLHIGH confidence
Cannot change nullable column to NOT NULL with existing NULL data
Production Risk
High — ALTER fails or data may be silently coerced in non-strict mode.
How to reproduce
trigger — this will error
trigger — this will error
UPDATE t SET col = 'default' WHERE col IS NULL; ALTER TABLE t MODIFY COLUMN col VARCHAR(100) NOT NULL;
expected output
ERROR 3614 (HY000): Cannot change column from nullable to NOT NULL; existing NULL data exists.
Fix
Backfill NULLs before altering
Backfill NULLs before altering
UPDATE t SET col = '' WHERE col IS NULL; ALTER TABLE t MODIFY COLUMN col VARCHAR(100) NOT NULL DEFAULT '';
Why this works
Eliminates NULL values before applying the NOT NULL constraint.
What not to do
✕
Version notes
Sources
Official documentation ↗
MySQL 8.0 — 3614 ER_CANNOT_CHANGE_COLUMN_FROM_NULLABLE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev