Informix Error -865

Reasons / Common Causes

-865 fires when ADD ROWIDS, DROP ROWIDS, ADD CRCOLS, or DROP CRCOLS is combined with any other ALTER TABLE option in the same statement — per the official guidance, these four keywords must each be the only option in their ALTER TABLE statement.

1. ADD ROWIDS/DROP ROWIDS/ADD CRCOLS/DROP CRCOLS combined with another ALTER TABLE clause (adding a column, dropping a column, adding a constraint, etc.) in the same statement, per the official guidance — the direct, only cause.

Solutions / Resolution

1. Split the statement into separate ALTER TABLE statements, one for the ROWID/CRCOLS-related keyword and one for every other option:

   ALTER TABLE orders ADD ship_date DATE;
   ALTER TABLE orders DROP ROWIDS;
   

   rather than combining both into a single statement.

Examples

The disallowed combination

ALTER TABLE orders ADD ship_date DATE, DROP ROWIDS;
-- -865: DROP ROWIDS can't be combined with ADD ship_date DATE

Corrected — two separate statements

ALTER TABLE orders ADD ship_date DATE;
ALTER TABLE orders DROP ROWIDS;

Diagnostic Checks

1. Check whether the ALTER TABLE statement combines ADD ROWIDS/DROP ROWIDS/ADD CRCOLS/DROP CRCOLS with any other option, and split it into separate statements if so.

Related Errors / Related Topics

• -855 — "Cannot drop rowids on a non-fragmented table." A related ROWID-management restriction, about which tables support the drop at all.
• -856 — "Rowids already exist on table." A related ROWID-management restriction, about redundant creation.
• -857 — "Rowids do not exist on table." A related ROWID-management restriction, about dropping a nonexistent ROWID.

ADD/DROP ROWIDS and ADD/DROP CRCOLS must each stand alone in their own ALTER TABLE statement — split combined statements apart.