Informix Error -677

-677 Check constraint cannot contain subqueries or procedures.

A check constraint cannot contain a subquery or a procedure. Subqueries are disallowed in check constraints because they depend on the state of the database. For data integrity, the evaluation of a check constraint must not vary or depend on the state of the database.

Example of error:

CREATE TABLE new_stock ( stock_num SERIAL PRIMARY KEY, unit_price MONEY CHECK (unit_price >= (SELECT unit_price FROM stock WHERE manu_code = 'HRO')), manu_code CHAR(3));

Verify that your statement does not have a subquery or procedure in a check constraint.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-677 fires when a CHECK constraint's expression includes a subquery or a procedure/function call — per the official guidance, this is a data-integrity requirement: a check constraint's result must depend only on the row being checked, not on the broader state of the database, which a subquery or routine call could introduce.

A CHECK expression including a SELECT subquery, per the official guidance — for example, checking a value against another table's contents.
A CHECK expression calling a user-defined function/procedure, per the official guidance — even a function that happens to be side-effect-free is disallowed here, since the constraint mechanism can't guarantee that in general.
An attempt to enforce a genuinely cross-row or cross-table business rule via CHECK, when that class of rule requires a trigger instead, since it depends on more than the row being validated.

Solutions / Resolution

Remove the subquery or procedure call from the CHECK expression, per the official guidance — express the rule using only the row's own column values and built-in, non-query expressions.
Use a trigger instead, if the rule genuinely needs to reference other rows, other tables, or a routine call — triggers can express rules CHECK constraints structurally can't.

Examples

The disallowed subquery

CREATE TABLE order_items (
  order_id INT CHECK (order_id IN (SELECT order_id FROM orders)),
  item_id INT
);
-- -677: subquery in a CHECK constraint

Corrected — a foreign key instead, for this kind of rule

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

Or a trigger, for rules a FOREIGN KEY can't express

CREATE TRIGGER trg_order_items_insert INSERT ON order_items
  REFERENCING NEW AS post FOR EACH ROW
  (EXECUTE PROCEDURE validate_order_item(post.order_id, post.item_id));

Diagnostic Checks

Scan the CHECK expression for a SELECT subquery or a function/procedure call, and redesign using a foreign key (for referential checks) or a trigger (for anything more complex) instead.

Related Errors / Related Topics

-676 — "Invalid check constraint column." A related CHECK restriction, about which columns a column-level check can reference.
-678 — "Invalid subscript for column column-name in check constraint." A related CHECK restriction, about subscript bounds.

A CHECK constraint's result can only depend on the row itself — use a foreign key or a trigger for anything that needs to look beyond it.