Oninit
Talk to an expert →

Informix Error -623

-623 Unable to find CONSTRAINT constraint-name.

The constraint specified in this statement cannot be found. Review the spelling of the name shown. If it is as you intended, check the names of existing named constraints by querying the sysconstraints table as shown in the following example. If the owner name shown for the desired constraint is not yours, you need to be a Database Administrator to reference it (and, if this database is ANSI compliant, you need to qualify the name with its owner-name).

SELECT constrname, owner FROM informix.sysconstraints

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-623 fires when a statement references a named constraint that doesn't exist — most often a misspelling, but per the official guidance, ownership and ANSI-qualification rules can also be the reason a constraint that does exist can't be found under the name given.

  1. A misspelled constraint name, per the official guidance — the most common, direct cause.
  2. A constraint owned by a different user, per the official guidance — referencing another user's constraint requires Database Administrator privilege.
  3. An unqualified constraint name in an ANSI-compliant database, per the official guidance — ANSI compliance requires qualifying the name with its owner name, so an unqualified reference to someone else's constraint won't resolve.
Solutions / Resolution
  1. Review the spelling of the constraint name, per the official guidance.
  2. List existing named constraints to check the correct name and owner, per the official guidance's documented query:
    SELECT constrname, owner FROM informix.sysconstraints;
  3. If the constraint is owned by a different user, either get DBA privilege or have that user perform the operation, per the official guidance.
  4. In an ANSI-compliant database, qualify the name with its owner, per the official guidance, if referencing a constraint owned by someone else:
    ALTER TABLE orders DROP CONSTRAINT other_owner.ck_orders_status;
Examples
Listing constraints to find the correct name/owner
SELECT constrname, owner FROM informix.sysconstraints;
An ANSI-compliant database requiring an owner-qualified name
ALTER TABLE orders DROP CONSTRAINT other_owner.ck_orders_status;
Diagnostic Checks
  1. Run the official sysconstraints query to confirm the constraint's exact name and owner.
  2. Check whether the database is ANSI-compliant, and whether the constraint's owner differs from the current session's user, if the name itself looks correct.
Related Errors / Related Topics
  • -625 — "Constraint name constraint-name already exists." The mirror-image situation: a constraint name that's already taken, rather than one that can't be found. Points back to this same sysconstraints listing query.

Usually a spelling mistake — but check ownership and ANSI-qualification requirements too, per the official guidance, before assuming the constraint simply doesn't exist.