Oninit
Talk to an expert →

Informix Error -287

-287 Cannot add serial column column-name to table.

You cannot add a column of SERIAL or SERIAL8 or BIGSERIAL data type to an existing table. Such columns may not contain null values, but when you add a column, the database server must put null values in all existing rows. You can add a serial column in three steps. First, add the column with an INTEGER data type. Then update the table with nonnull, unique values in each row of the new column. Finally, use ALTER TABLE MODIFY to change the data type of the column to SERIAL or SERIAL8 or BIGSERIAL.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-287 is the SERIAL-specific sibling of -269: the same root cause (existing rows would need a value, but the column type can't hold NULL), applied to SERIAL/SERIAL8/BIGSERIAL columns specifically. This one has a clean, fully documented three-step workaround.

  1. Attempting ALTER TABLE ADD COLUMN with a SERIAL/SERIAL8/BIGSERIAL type on an existing, populated table — not permitted, since the engine would need to put NULL into every existing row for the new column, and serial columns can never hold NULL.
  2. Not knowing the documented three-step workaround and attempting the direct ADD COLUMN as if it were an ordinary column type.
  3. Migration tooling generating ADD COLUMN ... SERIAL DDL generically, without accounting for this restriction.
Solutions / Resolution

Follow the official three-step sequence:

  1. Add the column with an INTEGER data type first.
  2. Update the table with nonnull, unique values in each row of the new column.
  3. Use ALTER TABLE MODIFY to change the column's data type to SERIAL, SERIAL8, or BIGSERIAL.
Examples
The full three-step sequence
-- Step 1: add as INTEGER
ALTER TABLE orders ADD sequence_num INTEGER;

-- Step 2: populate existing rows with unique, nonnull values
-- (a simple approach using ROWID as a temporary ordering basis)
UPDATE orders SET sequence_num = ROWID;

-- Step 3: convert to SERIAL
ALTER TABLE orders MODIFY (sequence_num SERIAL);
The disallowed direct attempt
ALTER TABLE orders ADD sequence_num SERIAL;
-- -287: existing rows would need NULL, which SERIAL doesn't allow
Diagnostic Checks
  1. Confirm the table already has rows — if it's empty, this restriction wouldn't apply at all, and a direct ADD COLUMN ... SERIAL would work.
  2. Review whether existing rows can be given unique, nonnull values for the new column before proceeding with step 2 of the workaround.
Related Errors / Related Topics
  • -269 — "Cannot add column column-name that does not accept nulls." The direct sibling — same underlying root cause, applied to ordinary NOT NULL columns rather than serial ones.
  • -232 — "A SERIAL column column-name may not be updated." Another SERIAL-column restriction, worth reviewing together since the workaround here depends on being able to populate the column before it becomes a serial type (step 2 happens while it's still a plain INTEGER, before -232's restriction applies).

Follow the three-step workaround exactly — add as INTEGER, populate, then convert with ALTER TABLE MODIFY — this is a fully documented, reliable path, not something to work around differently.