The following rules apply to using CHECK constraints with the ALTER TABLE statement.
- The following table explains rules for ADD and MODIFY.
THIS form … IS allowed only if column_name does … ADD column_name CHECK (boolean_condition) not already have a constraint. MODIFY have 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 ensure 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.
IF you execute this statement … THEN you add/drop the following number of unnamed table level CHECK constraints … ALTER TABLE ... DROP CHECK all. anything else one per ALTER TABLE request, regardless of constraint type. - A CHECK constraint can neither be defined for nor reference a LOB column.