Informix Error -735

Reasons / Common Causes

-735 fires when a DELETE/MERGE statement's correlated subquery (or a MERGE's source table) references a child table that a cascaded delete would also affect — per the official guidance, this is disallowed because the result would depend on row-processing order, which isn't guaranteed.

1. A child table (one with ON DELETE CASCADE back to the table being deleted from) referenced in a correlated subquery inside a DELETE, per the official guidance — the direct, common cause.
2. The same child table used as a MERGE statement's source table, per the official guidance — the analogous restriction for MERGE.
3. A query written without realizing a referenced table has a cascading foreign-key relationship to the table being modified, since the cascade relationship isn't visible in the DELETE/MERGE statement's own text.

Solutions / Resolution

1. Rewrite the query so the child table isn't referenced in a correlated subquery, or as a MERGE source table, or both, per the official guidance — this is the documented, only resolution.
2. Split the operation into separate steps, if the logic genuinely needs to reference the child table's data — capture what's needed into a temp table first, then perform the DELETE/MERGE referencing the temp table instead of the live child table directly.
3. Check which tables have a cascading foreign-key relationship to the table being modified, if it's not obvious from the query alone:

   SELECT c.tabname AS child_table, k.constrname
   FROM sysreferences r, sysconstraints k, systables c
   WHERE r.constrid = k.constrid
     AND k.tabid = c.tabid
     AND r.delrule = 'C'
     AND r.ptabid = (SELECT tabid FROM systables WHERE tabname = 'orders');
   -- delrule = 'C' identifies a cascading (ON DELETE CASCADE) foreign key
   

Examples

The disallowed reference

DELETE FROM orders WHERE order_id IN
  (SELECT order_id FROM order_items WHERE item_id = 5);
-- -735: order_items is a child table (ON DELETE CASCADE) of orders,
-- referenced in a correlated subquery of the DELETE

Corrected — stage into a temp table first

SELECT order_id FROM order_items WHERE item_id = 5 INTO TEMP orders_to_delete;
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM orders_to_delete);

Diagnostic Checks

1. Identify every child table with a cascading delete relationship to the table being modified, and check whether the DELETE/MERGE statement references any of them in a correlated subquery or as a MERGE source.

Related Errors / Related Topics

• -692 — "Key value for constraint constraint-name is still being referenced." A related referential-integrity error, about a plain delete blocked by a still-referenced key, distinct from this error's row-processing-order restriction on cascading relationships.

Stage the needed data into a temp table first if a cascading child table needs to be referenced in the same DELETE/MERGE.