Oninit
Talk to an expert →

Informix Error -386

-386 Column contains null values.

This ALTER TABLE statement contains a MODIFY clause that assigns the NOT NULL attribute to an existing column. However, that column already contains one or more null values. The modification cannot be made until the null values have been deleted or updated to some nonnull value.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-386 fires on ALTER TABLE ... MODIFY when adding a NOT NULL constraint to a column that already has NULL values in existing rows — the server can't retroactively enforce a constraint against data that already violates it.

  1. A column with existing NULL values in some rows, while the ALTER TABLE ... MODIFY attempts to add NOT NULL to it.
  2. A column that was nullable by original design, later needing to become mandatory as requirements evolved, without first cleaning up the existing null rows.
  3. Incomplete data migration or backfill — rows added before a required field's business rule was finalized, left with NULL in that column.
Solutions / Resolution
  1. Update the existing NULL values to a valid non-null value first, per the official guidance, before attempting to add NOT NULL.
  2. Identify which rows have NULL values:
    SELECT * FROM orders WHERE shipping_status IS NULL;
  3. Backfill those rows with an appropriate default or computed value, then retry the ALTER TABLE ... MODIFY.
  4. If some rows genuinely can't have a value backfilled, consider whether NOT NULL is actually appropriate, or whether a default value should be added instead.
Examples
Backfilling nulls before adding NOT NULL
SELECT COUNT(*) FROM orders WHERE shipping_status IS NULL;
-- confirm how many rows need attention

UPDATE orders SET shipping_status = 'pending' WHERE shipping_status IS NULL;

ALTER TABLE orders MODIFY (shipping_status VARCHAR(20) NOT NULL);
The disallowed direct attempt
ALTER TABLE orders MODIFY (shipping_status VARCHAR(20) NOT NULL);
-- -386: some existing rows have NULL in shipping_status
Diagnostic Checks
  1. Query for existing NULL values in the target column before attempting the MODIFY.
  2. Decide on an appropriate backfill value for those rows, consistent with the application's actual business logic.
Related Errors / Related Topics
  • -292 — "An implied insert column column-name does not accept NULLs." A related NOT NULL-enforcement error, at insert time rather than during a retroactive schema change.
  • -269 — "Cannot add column column-name that does not accept nulls." A closely related restriction — adding a new NOT NULL column to a populated table hits a similar underlying issue as modifying an existing column to NOT NULL.

Backfill existing NULL values before adding a NOT NULL constraint — the server won't retroactively accept data that already violates the constraint being added.