Informix Error -536

Reasons / Common Causes

-536 fires when a FOREIGN KEY constraint's referencing (child) column list has a different number of columns than the referenced (parent) key it points to — a composite foreign key must map one-to-one, column-for-column, onto the parent's key.

1. A composite foreign key listing fewer or more columns than the parent's primary/unique key — the direct, only cause.
2. A schema change that added or removed a column from the parent key, without updating foreign keys elsewhere that reference it.
3. Copy-pasted FOREIGN KEY DDL from a different relationship, where the column count happened to differ from the parent key actually being referenced here.

Solutions / Resolution

1. Check that a one-to-one relationship exists between referenced and referencing columns, per the official guidance, by comparing the two column lists directly.
2. Add or remove columns from the child key list so its count matches the parent key's column count exactly.
3. Confirm the parent key's actual column list before rewriting the foreign key, in case a schema change altered it. sysindexes.part1 through part16 hold the colno of each key part (negative for descending order) for the index backing the parent constraint:

   SELECT i.part1, i.part2, i.part3 FROM sysconstraints k, sysindexes i
   WHERE k.idxname = i.idxname AND k.constrname = 'parent_key_constraint_name';
   

   then match each non-null partN (its absolute value) against syscolumns.colno for the same tabid to get the actual column names.

Examples

Mismatched column counts

CREATE TABLE orders (order_id INT, region_id INT, PRIMARY KEY (order_id, region_id));

CREATE TABLE order_items (
  item_id INT,
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id, region_id)
);
-- -536: child key has 1 column, parent key has 2

Corrected

CREATE TABLE order_items (
  item_id INT,
  order_id INT,
  region_id INT,
  FOREIGN KEY (order_id, region_id) REFERENCES orders(order_id, region_id)
);

Diagnostic Checks

1. Count the columns in the FOREIGN KEY (...) list and the parent's REFERENCES table(...) list side by side before running the CREATE TABLE/ALTER TABLE statement.

Related Errors / Related Topics

• -537 — "Constraint column column-name not found in table." A related but distinct constraint-definition error: a named column that doesn't exist at all, rather than a count mismatch between two existing column lists.

A column-count mismatch between the child and parent key lists — recount both sides against the parent's actual key definition.