Informix Error -269

Reasons / Common Causes

-269 is a well-defined restriction: adding a NOT NULL column to a table that already has rows doesn't work the way defining one on a brand-new table does — the engine has no value to put in that column for the existing rows, and NOT NULL means null isn't acceptable.

1. ALTER TABLE ADD COLUMN with a NOT NULL constraint on a table that already has rows — the direct cause.
2. Not realizing adding a NOT NULL column to a populated table is fundamentally different from defining one on an empty table at creation time.
3. Schema-migration tooling generating ADD COLUMN ... NOT NULL DDL generically, without checking whether the target table already has rows.

Solutions / Resolution

1. Follow the documented workaround: define a new table that includes the desired column, then INSERT the data from the old table into it, supplying suitable nonnull values for the new column as part of that insert.
2. Check whether your specific version supports ALTER TABLE ADD COLUMN ... NOT NULL DEFAULT <value> — many current versions can supply a default value for existing rows automatically, which is often more convenient than the new-table-and-copy approach. Verify this against your version's documentation, since it's not the fix the official text names directly.
3. For migration tooling, detect populated tables before generating ADD COLUMN ... NOT NULL DDL, and either supply a DEFAULT value or fall back to the new-table-and-copy approach automatically.

Examples

The documented workaround: new table and copy

CREATE TABLE customer_new
(
    id      INTEGER,
    name    VARCHAR(50),
    status  VARCHAR(20) NOT NULL
);

INSERT INTO customer_new (id, name, status)
  SELECT id, name, 'active' FROM customer;
-- 'active' supplies a suitable nonnull value for every existing row

DROP TABLE customer;
RENAME TABLE customer_new TO customer;

A modern alternative, if supported

ALTER TABLE customer ADD status VARCHAR(20) NOT NULL DEFAULT 'active';
-- check version documentation to confirm support before relying on this

Diagnostic Checks

1. Confirm the table already has rows — if it's empty, this restriction wouldn't apply at all, and the actual cause is something else.
2. Check whether the target version supports a DEFAULT clause for NOT NULL columns added via ALTER TABLE.

Related Errors / Related Topics

• -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.
• -236 — "Number of columns in INSERT does not match number of VALUES." Relevant to the new-table-and-copy workaround's INSERT ... SELECT step.

Check whether your version supports DEFAULT on ALTER TABLE ADD COLUMN before reaching for the new-table-and-copy workaround — it's often the simpler fix if available.