Check constraints

 

You use check constraints to maintain limits on field values so that they conform to your database requirements.

Check constraints ensure data validity during insert or update operations by checking the data against a check constraint expression that you define.

For example, you can create a check constraint on a field and define that the values that are inserted into the field must be between 1 and 100. If a value does not fall within the range, the insert or update operation against your database is not processed. Check constraints are much like referential constraints in terms of their states:

• Defined and enabled. The constraint definition has been added to the file, and the constraint will be enforced after the constraint is established.

• Defined and disabled . The constraint definition has been added to the file, but the constraint will not be enforced.

• Established and enabled. The constraint has been added to the file and all of the pieces of the file are there for enforcement.

• Established and disabled. The constraint has been added to the file and all of the pieces of the file are there for enforcement, but the constraint will not be enforced.

A check constraint, like a referential constraint, can have a check pending status. If the data in any field violates the check constraint expression, then the constraint is in check pending status. For the insertion or update of a record, if the data violates the check constraint expression, then the insert or update operation is not allowed. A check constraint that contains one or more Large Object (LOB) fields is restricted to a narrower range of operations than a check constraint without LOB fields. When the check constraint includes one or more LOB fields, the LOB fields can only be involved in direct comparisons to:

• Other LOB fields of the same type and same maximum length.

• Literal values.

• The null value.

Operations known as derived operations, such as the Substring or Concat operations, are not allowed against LOB fields in a check constraint. The diagnostic message CPD32E6 will be sent when you try to add a check constraint that attempts a derived operation against a LOB field.

 

Parent topic:

Controlling the integrity of your database with constraints