Informix Error -742: Trigger and cascading-delete referential constraint cannot coexist.

Reasons / Common Causes

-742 fires in either direction of the same conflict, per the official guidance: adding an ON DELETE CASCADE foreign key to a table that already has a delete trigger, or adding a delete trigger to a table that already has an ON DELETE CASCADE foreign key — the two mechanisms can't coexist on the same table.

1. ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY ... ON DELETE CASCADE attempted on a table that already has a DELETE trigger, per the official guidance — the first direction of the conflict.
2. CREATE TRIGGER ... DELETE ON ... attempted on a table that already has a cascading-delete foreign key, per the official guidance — the second direction.
3. A schema evolving incrementally, where a delete trigger and a cascading foreign key were added by different people at different times, each unaware the other mechanism was already in place.

Solutions / Resolution

1. Choose one mechanism, per the official guidance's implicit resolution — either a delete trigger or a cascading foreign key, not both, on the same table.
2. If a cascading foreign key already exists, express the delete-time logic inside the foreign key relationship instead of a trigger — or, if trigger-based logic is genuinely required, remove ON DELETE CASCADE and have the trigger perform the equivalent cleanup explicitly.
3. If a delete trigger already exists, add the equivalent cascading-delete logic to the trigger's own action instead of adding a separate cascading foreign key.
4. Check for both before adding either:

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

Examples

The disallowed combination

CREATE TRIGGER trg_orders_delete DELETE ON orders ...;
ALTER TABLE order_items ADD CONSTRAINT
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE;
-- -742: orders already has a delete trigger

Corrected — trigger performs the cleanup instead of CASCADE

CREATE TRIGGER trg_orders_delete DELETE ON orders
  REFERENCING OLD AS pre FOR EACH ROW
  (DELETE FROM order_items WHERE order_id = pre.order_id);

ALTER TABLE order_items ADD CONSTRAINT
  FOREIGN KEY (order_id) REFERENCES orders(order_id);  -- no ON DELETE CASCADE

Diagnostic Checks

1. Check for an existing delete trigger and an existing cascading-delete foreign key on the table before adding either mechanism, via systriggers/sysconstraints.

Related Errors / Related Topics

• -741 — "Trigger for the same event already exists." A related trigger-restriction error, about multiple triggers for the same event rather than a trigger conflicting with a cascading foreign key.

Pick one mechanism — a delete trigger or ON DELETE CASCADE — and fold the other's logic into it if both behaviors are genuinely needed.