Oninit
Talk to an expert →

Informix Error -592

-592 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.