Informix Error -691

Reasons / Common Causes

-691 is the everyday foreign-key-violation error: it fires on INSERT/UPDATE when the value being written to a referencing (child-key) column doesn't exist in the referenced (parent-key) column — per the official guidance, the same cascading-deletes-require-logging requirement noted for -690 also applies here.

1. An INSERT/UPDATE supplying a child-key value with no matching row in the parent table — per the official guidance, the ordinary, most common cause.
2. The parent row deleted or its key changed after the child row was written, before the constraint existed or while it was disabled, only surfacing now that the constraint is active and checking.
3. Cascading deletes attempted on an unlogged database, per the official guidance's shared note with -690 — logging must be on for cascading deletes to work.

Solutions / Resolution

1. Confirm the parent-key value actually exists before inserting/updating the child row:

   SELECT * FROM orders WHERE order_id = 42;
   

2. Insert the parent row first, if the child row was written out of order.
3. Check is_logging on the database (per -690) if cascading deletes are involved and the error persists unexpectedly.
4. Find all orphaned child rows already in the table, per the pattern used for -525, if the violation surfaces while adding the constraint to existing data rather than on a fresh insert:

   SELECT c.* FROM order_items c
   LEFT JOIN orders p ON c.order_id = p.order_id
   WHERE p.order_id IS NULL;
   

Examples

The ordinary violation

INSERT INTO order_items (order_id, item_id) VALUES (999, 1);
-- -691: no orders row with order_id 999

Confirming the parent exists first

SELECT * FROM orders WHERE order_id = 999;
-- no rows -- insert the parent first, or correct the child's order_id

Diagnostic Checks

1. Query the parent table for the specific key value being inserted/updated in the child.
2. Check is_logging if cascading deletes are involved.

Related Errors / Related Topics

• -525 — "Failure to satisfy referential constraint constraint-name." A related error, for the same underlying violation surfacing when a constraint is added/re-enabled against existing data, rather than on an ordinary insert/update.
• -690 — "Cannot read keys from referencing table table-name." A related referential- integrity error, about being unable to validate the constraint at all, sharing this error's cascading-deletes-logging requirement.
• -692 — "Key value for constraint constraint-name is still being referenced." The mirror-image violation: deleting a parent row that's still referenced, rather than inserting an orphaned child.

The everyday foreign-key violation — confirm the parent-key value exists before writing the child row that references it.