Informix Error -297

Reasons / Common Causes

-297 means Informix looked in sysconstraints for something to attach the foreign key to on the referenced table and came up empty. Critically, a bare REFERENCES table_name (with no column list) only works if the referenced table has a primary key — a plain unique constraint isn't enough for that shorthand form.

1. The referenced table has no primary key and no unique constraint at all — there's genuinely nothing to reference.
2. The referenced table has a unique constraint but not a primary key, and the foreign key used the bare REFERENCES table_name form instead of explicitly naming the column(s).
3. The referenced constraint and the referencing foreign key were meant to be created in the same ALTER TABLE statement, but weren't — the official text notes this specific case is also not found via sysconstraints lookup.
4. A typo in the referenced column name, when using the explicit REFERENCES table (column) form, so it doesn't match any actual unique/primary-key column.

Solutions / Resolution

1. If the referenced table has a unique constraint but no primary key, use the explicit form naming the column(s): REFERENCES table_name (column_name), per the official guidance — the bare form only works against a primary key.
2. If the referenced table has neither, add a primary key or unique constraint to it first.
3. Verify the column names and their associated constraints on the referenced table before retrying:

   SELECT c.constrname, c.constrtype, col.colname
   FROM sysconstraints c, sysindexes i, syscolumns col
   WHERE c.tabid = (SELECT tabid FROM systables WHERE tabname = 'parent_table')
     AND c.idxname = i.idxname
     AND col.tabid = c.tabid AND col.colno = i.part1;
   

4. If both constraints were meant to land in the same ALTER TABLE, split them into separate statements — add the referenced table's key constraint first, then the foreign key afterward.

Examples

Referenced table has a unique constraint but no primary key

CREATE TABLE customers (
    customer_id INTEGER UNIQUE,
    name        VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers
);
-- -297: customers has no primary key, so the bare
-- REFERENCES form doesn't resolve

Fix — name the column explicitly:

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

Referenced table has neither constraint

CREATE TABLE customers (
    customer_id INTEGER,
    name        VARCHAR(50)
);
-- no PRIMARY KEY, no UNIQUE constraint

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id)
);
-- -297: no unique constraint or primary key exists on customer_id at all

Fix — add a unique constraint or primary key first:

ALTER TABLE customers ADD CONSTRAINT PRIMARY KEY (customer_id);

Diagnostic Checks

1. Check whether the referenced table has a primary key or only a unique constraint:

   SELECT constrtype FROM sysconstraints WHERE tabid =
       (SELECT tabid FROM systables WHERE tabname = 'parent_table');
   

   P = primary key (bare REFERENCES table_name works), U = unique constraint only (explicit column form required).
2. If using the bare REFERENCES table_name form, confirm a primary key actually exists — switch to the explicit column form if only a unique constraint is present.
3. If both constraints were added in the same statement, split them into separate ALTER TABLE statements.

Related Errors / Related Topics

• -296 — "Referenced table table-name not available." A related referential-integrity failure, though about the table itself being unreachable rather than lacking a suitable key.
• -691 — "Missing index for foreign key foreign-key-tag in a referenced table." Another foreign-key-setup error in the same general family, about missing supporting index structures.

If the referenced table only has a unique constraint (not a primary key), the foreign key must name the column explicitly — the shorthand bare-table-name form only resolves against a primary key.