Informix Error -537

Reasons / Common Causes

-537 fires when a constraint definition (a PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK naming specific columns) references a column name that doesn't exist on the table.

1. A misspelled column name in the constraint's column list — the most common, direct cause.
2. A constraint written against a column that was renamed or dropped in an earlier schema change, without updating the constraint DDL to match.
3. Copy-pasted constraint DDL from a similar but differently-shaped table, carrying over a column name that doesn't exist on this particular table.

Solutions / Resolution

1. Review the column name in the constraint definition against the table's actual columns, correcting any misspelling.
2. Confirm the table's current column list before writing the constraint:

   SELECT colname FROM syscolumns
   WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'orders')
   ORDER BY colno;
   

3. Update the constraint to reference the column's current name, if it was renamed since the constraint DDL was originally written.

Examples

A misspelled column name

ALTER TABLE orders ADD CONSTRAINT
  CHECK (order_staus IN ('pending','shipped')) CONSTRAINT ck_orders_status;
-- -537: 'order_staus' -- the actual column is 'status'

Corrected

ALTER TABLE orders ADD CONSTRAINT
  CHECK (status IN ('pending','shipped')) CONSTRAINT ck_orders_status;

Diagnostic Checks

1. Query syscolumns for the table's actual column names and compare them character-by- character against the constraint's column references.

Related Errors / Related Topics

• -536 — "Number of columns in child constraint does not match number of cols in parent constraint." A related but distinct constraint-definition error: a count mismatch between two existing column lists, rather than a column name that doesn't exist at all.

A straightforward name mismatch — query syscolumns for the table's real column names before retrying.