Add Check Constraints -- Fields and controls

Check condition

Check condition

Use this box to specify the check condition for the constraint. Type the condition in the box.

Example: DEPTNO BETWEEN 10 AND 100

A check condition is a search condition, with the following restrictions:

• It can refer only to columns of the table.
• It can be up to 3800 characters long, not including redundant blanks.
• It must not contain any:
  • Subselects
  • Built-in or user defined functions
  • Host variables
  • Parameter markers
  • Special registers
  • Columns that include a field procedure
  • CASE expressions
  • Quantified predicates
  • EXISTS predicates
• If the check condition refers to a long string column, the reference must occur within a LIKE predicate.
• The AND and OR logical operators can be used between predicates. The NOT logical operator cannot be used.
• The first operand of every predicate must be the column name of a column in the table.
• The second operand in the check condition must be either a constant or the name of a column in the table:

  • If the second operand of a predicate is a constant, and if the constant is:

    • A decimal number, then the column data type must be either floating point or decimal.
    • An integer number, then the column data type must not be small integer.
    • A small integer number, then the column data type must be small integer.
    • A decimal constant, then its precision must not be larger than the precision of the column.
  • If the second operand of a predicate is a column, then both columns of the predicate must have both:

    • The same data type.
    • Identical descriptions with the exception that the nullable attribute and defaults can be different, and that string columns with the same data type can have different lengths.
• A check condition can evaluate to unknown if a column that is an operand of the predicate is null. This does not violate the check constraint.