17.10 - Adding and Dropping CHECK Constraints - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.