Informix Error -785

Reasons / Common Causes

-785 fires when ALTER TABLE ... DROP is attempted on a column that's referenced by the table's (or one of its indexes') fragmentation expression — per the official guidance, the fragmentation expression must be altered to remove references to the column before the column itself can be dropped.

1. The column being dropped is used in the table's FRAGMENT BY EXPRESSION clause, per the official guidance — the direct, common cause.
2. The column is used in a fragmented index's own fragmentation expression, distinct from the table's fragmentation scheme.

Solutions / Resolution

1. Alter the fragmentation expression to remove references to the column first, per the official guidance:

   ALTER FRAGMENT ON TABLE orders INIT FRAGMENT BY EXPRESSION
     (order_id < 1000) IN dbspace1, (order_id >= 1000) IN dbspace2;
   -- (a new scheme that no longer references the column to be dropped)
   

2. Then drop the column, per the official guidance's implicit next step, once the fragmentation expression no longer depends on it:

   ALTER TABLE orders DROP region;
   

3. Check both the table's and any fragmented index's expressions for references to the column, since either could block the drop independently.

Examples

The disallowed attempt

-- orders is FRAGMENT BY EXPRESSION (region = 'east') IN east_db, (region = 'west') IN west_db
ALTER TABLE orders DROP region;
-- -785: region is referenced by the table's fragmentation expression

Corrected — re-fragment without the column, then drop it

ALTER FRAGMENT ON TABLE orders INIT FRAGMENT BY EXPRESSION
  (order_id < 1000) IN east_db, (order_id >= 1000) IN west_db;
ALTER TABLE orders DROP region;

Diagnostic Checks

1. Check the table's fragmentation expression for a reference to the column being dropped, and check any fragmented indexes' expressions as well.

Related Errors / Related Topics

• -676 — "Invalid check constraint column." A related restriction on where a column can be referenced (check constraints), rather than fragmentation expressions.

A dependency-ordering issue — re-fragment (or re-index) to remove the column reference first, then drop the column.