Informix Error -525

Reasons / Common Causes

-525 fires when ALTER TABLE (adding a new foreign key) or SET CONSTRAINTS (re-enabling a previously disabled one) is applied to a table whose existing data already violates that constraint — unlike a normal INSERT/UPDATE-time foreign-key check, this fires against data already sitting in the table.

1. Adding a foreign key via ALTER TABLE ... ADD CONSTRAINT to a table that already contains orphaned child rows — child-key values with no matching parent-key row.
2. Re-enabling a constraint that was disabled (SET CONSTRAINTS ... DISABLED) for a bulk load, where the loaded data was never actually checked against the parent table.
3. A parent-table row deleted or changed after child rows referencing it were inserted, while the constraint was disabled or before it existed, leaving orphaned references.

Solutions / Resolution

1. Find the rows in the referencing (child) table with no matching parent-key row, per the official guidance's suggested check:

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

2. Correct the orphaned rows — either delete them, or update them to reference a valid parent row — before the constraint can be added or re-enabled.
3. Retry ALTER TABLE/SET CONSTRAINTS once the child data is consistent with the parent table.

Examples

Hitting the violation

ALTER TABLE order_items ADD CONSTRAINT
  FOREIGN KEY (order_id) REFERENCES orders(order_id) CONSTRAINT fk_order_items_orders;
-- -525: some order_items.order_id values have no matching orders row

Finding and fixing the orphaned rows

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

DELETE FROM order_items WHERE order_id NOT IN (SELECT order_id FROM orders);

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

Diagnostic Checks

1. Run an anti-join between the child and parent tables (as above) to find every orphaned child-key value before attempting to add or re-enable the constraint.
2. Check whether the constraint was ever disabled (SET CONSTRAINTS ... DISABLED) for a bulk load, since that's a common way orphaned data enters undetected.

Related Errors / Related Topics

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

Existing data violates the constraint being added or re-enabled — find the orphaned child rows with an anti-join against the parent table before retrying.