15.00 - Adding and Dropping CHECK Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Adding and Dropping CHECK Constraints

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.


    have a constraint.

    Note: 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.

  • The following form drops all unnamed column level CHECK constraints on column_name.
  •    ALTER TABLE table_name DROP column_name CHECK 
  • The following form drops all unnamed table level CHECK constraints on the table name.
  •    ALTER TABLE table_name DROP CHECK 
  • Either of the following is legitimate syntax for dropping a named CHECK constraint.
  • 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 perform this statement …

    THEN you add/drop the following number of unnamed table level CHECK constraints …



    anything else

    one per ALTER TABLE request, regardless of constraint type.

  • A CHECK constraint can neither be defined for nor reference a LOB column.