15.00 - Adding and Dropping CHECK Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

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.

    MODIFY

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