Oninit
Talk to an expert →

Informix Error -530: Check constraint constraint-name failed.

Cause and resolution

Check constraint constraint-name failed.

The check constraint placed on the table column was violated. To see the check constraint associated with the column, query the syschecks system catalog table. However, you must know the constrid for the check constraint before you query syschecks. (The constrid is assigned in the sysconstraints system catalog table.) Use the following subquery to show the check constraint for constraint-name:

SELECT * FROM syschecks WHERE constrid = (SELECT constrid FROM sysconstraints WHERE constrname = constraint-name)

In certain scenarios, you might get this error without a constraint name being specified. This can happen if you are performing a DDL operation that has a check constraint that is violated by the existing data on the table. In this case, the DDL operation would not succeed, and thus the constraint name would not be in the system catalog.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-530 fires when an INSERT or UPDATE's data violates a CHECK constraint defined on the table, or when a DDL operation (adding/re-enabling a check constraint) finds existing data that already violates it.

  1. An INSERT/UPDATE supplying a value that fails the column's/table's CHECK expression — the ordinary, most common cause.
  2. ALTER TABLE ... ADD CONSTRAINT CHECK (...) against a table with existing data that already violates the new constraint — per the official guidance, this can surface -530 without a constraint name, since the DDL never succeeded and the constraint was never actually recorded in the catalog.
  3. Re-enabling a previously disabled check constraint (SET CONSTRAINTS ... ENABLED) against data that drifted out of compliance while the constraint was disabled.
Solutions / Resolution
  1. Look up the check constraint's actual expression, per the official guidance, when a constraint name is given:
    SELECT * FROM syschecks WHERE constrid =
  (SELECT constrid FROM sysconstraints WHERE constrname = 'constraint-name');
  2. Correct the value being inserted/updated so it satisfies the constraint's expression, once the expression is known.
  3. For a DDL-time failure with no constraint name given, per the official guidance, find the existing rows that would violate the new/re-enabled constraint and correct them first, since the constraint was never actually added to the catalog for the lookup above to work.
Examples
An ordinary INSERT-time violation
-- orders has: CHECK (status IN ('pending','shipped','cancelled'))
INSERT INTO orders (order_id, status) VALUES (42, 'unknown');
-- -530: Check constraint ck_orders_status failed.
Looking up the constraint's definition
SELECT * FROM syschecks WHERE constrid =
  (SELECT constrid FROM sysconstraints WHERE constrname = 'ck_orders_status');
A DDL-time failure with no constraint name
ALTER TABLE orders ADD CONSTRAINT CHECK (status IN ('pending','shipped','cancelled'))
  CONSTRAINT ck_orders_status;
-- -530, but ck_orders_status was never recorded in sysconstraints since the
-- ALTER TABLE didn't succeed -- find and fix violating rows directly instead:

SELECT order_id, status FROM orders
WHERE status NOT IN ('pending','shipped','cancelled');
Diagnostic Checks
  1. Query syschecks/sysconstraints for the constraint's expression when a constraint name is available, per the official guidance's documented subquery.
  2. When no constraint name is given (a DDL-time failure), reconstruct the intended check expression from the ALTER TABLE statement itself and query the table directly for violating rows.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -530 in this set yet.

Look up the constraint's expression via syschecks/sysconstraints when a name is given; for a nameless DDL-time failure, find violating rows directly against the intended expression instead.