The following rules apply to using CHECK constraints with the ALTER TABLE statement.
- ADD column_name CHECK (boolean_condition) is allowed only if column_name does not have a constraint.
- MODIFY is allowed only if column_name has a constraint.Only one CHECK modification can be made per ALTER TABLE request and that CHECK and non-CHECK alterations cannot be combined within a single ALTER TABLE request.
- To drop all unnamed column level CHECK constraints on column_name:
ALTER TABLE table_name DROP column_name CHECK
- To drop all unnamed table level CHECK constraints on the table name:
ALTER TABLE table_name DROP CHECK - To drop a named CHECK constraint, use either of the following statements:
DROP CONSTRAINT name CHECK DROP CONSTRAINT name
- The following form is valid only if a constraint with name already exists in the table.
MODIFY CONSTRAINT name CHECK (search condition)
This also applies to the named constraints defined as part of the column definition because those constraints are handled as named table-level constraints.
- To make sure maximum system performance, there is a limit of 100 table-level constraints that can be defined for any table.
A combination of table-level, column-level, and WITH CHECK on view constraints can create a constraint expression that is too large to be parsed for INSERT and UPDATE requests.
- CHECK constraints are not supported for global temporary tables.
- Details of adding and dropping constraints with ALTER TABLE are explained in the following table:
Statement Unnamed Table-Level CHECK Constraints Added or Dropped ALTER TABLE ... DROP CHECK All. Any other One per ALTER TABLE request, regardless of constraint type. - A CHECK constraint can neither be defined for nor reference a LOB column.