Informix Error -784

Reasons / Common Causes

-784 fires when ALTER FRAGMENT ... DETACH is attempted on a dbspace/partition or table that has existing referential constraints — per the official guidance, these constraints must be dropped before the detach can proceed.

1. A foreign key referencing (or referenced by) the fragment/table being detached, per the official guidance — the direct, only cause.
2. A detach attempted without accounting for the referential relationships the fragment's data participates in, since detaching effectively removes that data from the parent table's scope.

Solutions / Resolution

1. Drop the referential constraints before performing the detach, per the official guidance:

   ALTER TABLE order_items DROP CONSTRAINT fk_order_items_orders;
   ALTER FRAGMENT ON TABLE orders DETACH east_partition orders_east;
   

2. Re-establish the referential constraint afterward, if it's still needed and the detached data's new table (or the remaining fragmented table) should still enforce it:

   ALTER TABLE order_items ADD CONSTRAINT
     FOREIGN KEY (order_id) REFERENCES orders(order_id) CONSTRAINT fk_order_items_orders;
   

3. Identify every referential constraint involving the fragment/table before attempting the detach:

   SELECT constrname FROM sysconstraints WHERE tabid =
     (SELECT tabid FROM systables WHERE tabname = 'orders') AND constrtype = 'R';
   

Examples

Dropping the constraint, detaching, then re-adding it

ALTER TABLE order_items DROP CONSTRAINT fk_order_items_orders;
ALTER FRAGMENT ON TABLE orders DETACH east_partition orders_east;
ALTER TABLE order_items ADD CONSTRAINT
  FOREIGN KEY (order_id) REFERENCES orders(order_id) CONSTRAINT fk_order_items_orders;

Diagnostic Checks

1. Query sysconstraints for referential constraints (constrtype = 'R') involving the table/fragment being detached, before attempting the detach.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -784 in this set yet.

Drop the referential constraint first, detach, then re-add the constraint afterward if it's still needed.