16.20 - Adding and Dropping CHECK Constraints - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

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
  • 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.