Informix Error -295

Reasons / Common Causes

-295 is a hard limit on referential constraints: a foreign key's referencing table and its referenced (parent) table must both live in the same database. Informix does not support cross-database foreign keys, even when both databases are on the same server instance.

1. A CREATE TABLE or ALTER TABLE ADD CONSTRAINT referencing a table qualified with a different database name — e.g. REFERENCES otherdb:parent_table.
2. A multi-database schema design assuming cross-database referential integrity is enforceable, when in practice it isn't — this has to be handled at the application level instead.
3. A table moved to a different database (via RENAME across databases, or dropped and recreated elsewhere) without updating the foreign keys that referenced it, now leaving them pointing cross-database.

Solutions / Resolution

1. Keep the referencing and referenced tables in the same database, per the official guidance — this is a hard constraint, not something that can be worked around with syntax.
2. If the two tables genuinely need to live in separate databases, drop the formal foreign key constraint and enforce the relationship at the application level instead (or via triggers that perform the cross-database check manually).
3. If this appeared after a table was moved to a different database, either move it back, or redesign the referencing table's constraints to match its new location.

Examples

The disallowed cross-database reference

-- Table lives in database "sales"
CREATE TABLE sales:orders (
    order_id   INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES crm:customers(customer_id)
);
-- -295: crm:customers is in a different database than sales:orders

Fix — keep both tables in the same database:

CREATE TABLE sales:orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES sales:customers(customer_id)
);

Enforcing the relationship without a formal constraint

-- If customers must genuinely stay in a separate database,
-- drop the foreign key and validate in application code or
-- with a trigger that queries the other database explicitly.
CREATE TABLE sales:orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL
);

Diagnostic Checks

1. Check the database qualifier on both sides of the constraint — the referencing table and the referenced table must resolve to the same database name.
2. If the constraint used to work, check for a recent table move (dropped/recreated or renamed into a different database) that broke the pairing.

Related Errors / Related Topics

• -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

There's no syntax workaround here — a foreign key genuinely requires both tables to be in the same database; a cross-database relationship has to be enforced outside the constraint system.