Oninit
Talk to an expert →

Informix Error -592: Cannot specify column to be not null when the default value is null.

Cause and resolution

Cannot specify column to be not null when the default value is null.

This CREATE or ALTER TABLE statement specifies that a column may not contain nulls (the NOT NULL clause), but it also has a DEFAULT clause giving the default value for new rows as NULL. This contradiction is not allowed.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-592 fires when a column definition combines NOT NULL with DEFAULT NULL — a direct contradiction: NOT NULL forbids the column from ever holding NULL, while DEFAULT NULL would set it to NULL on every row that doesn't specify a value.

  1. NOT NULL and DEFAULT NULL both specified on the same column definition, per the official guidance — the direct, only cause.
  2. A DEFAULT clause left over from before NOT NULL was added to a column definition (or vice versa), during iterative schema editing, without noticing the two now conflict.
  3. Generated DDL combining a "not null" flag and a "no default" flag where "no default" was translated as DEFAULT NULL instead of simply omitting the clause.
Solutions / Resolution
  1. Remove the DEFAULT NULL clause if the column should genuinely never allow nulls and should require an explicit value on every insert.
  2. Or remove NOT NULL if a NULL default was actually intended, allowing the column to be NULL when not explicitly specified.
  3. If a non-NULL default is what was actually intended alongside NOT NULL, specify that value instead of NULL:
    status CHAR(10) NOT NULL DEFAULT 'pending'
Examples
The disallowed combination
CREATE TABLE orders (order_id INT, status CHAR(10) NOT NULL DEFAULT NULL);
-- -592: NOT NULL contradicts DEFAULT NULL
Corrected — a real default value
CREATE TABLE orders (order_id INT, status CHAR(10) NOT NULL DEFAULT 'pending');
Or corrected — drop NOT NULL if NULL default is intended
CREATE TABLE orders (order_id INT, status CHAR(10) DEFAULT NULL);
Diagnostic Checks
  1. Scan column definitions for both NOT NULL and DEFAULT NULL appearing together, and decide which one reflects the actual intent.
Related Errors / Related Topics
  • -591 — "Invalid default value for column/variable column-name/variable-name." The more general DEFAULT-clause type/length mismatch error, of which this NOT NULL/DEFAULT NULL conflict is a specific case.

A direct logical contradiction between the two clauses — pick a real default value, or drop NOT NULL if a NULL default is genuinely intended.