HELP CONSTRAINT Examples | Teradata Vantage - HELP CONSTRAINT Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Example: CHECK Constraint

This example shows the report returned for a table with a CHECK constraint.

     HELP CONSTRAINT table_1.check_1; 
            Name check_1
            Type CHECK
      Constraint CHECK(column_1 > 0 AND column_2 > 0)

Example: Temporal CHECK Constraints

Assume the following table definition.

     CREATE TABLE temporal.department (
       dept_name           VARCHAR(10),
       dept_no             INTEGER NOT NULL UNIQUE,
       dept_duration       PERIOD(DATE) AS VALIDTIME,
     CONSTRAINT vt_check_1 NONSEQUENCED VALIDTIME CHECK(dept_no < 100))
     PRIMARY INDEX (dept_name);

The following is the output of a HELP CONSTRAINT on this table using BTEQ.

     HELP CONSTRAINT temporal.department.vtcheck1;
      *** Help information returned. One row.
      *** Total elapsed time was 1 second.
      Name vt_check_1
      Type CHECK
Constraint CONSTRAINT vt_check_1 NONSEQUENCED VALIDTIME CHECK
           (dept_no<100)

Example: REFERENTIAL Constraint

The following example shows the report returned for a table with REFERENTIAL constraints.

     HELP CONSTRAINT table_1.reference_1;
                    Name reference_1
                    Type REFERENCES
                   State VALID
                Index Id 4
              FK Columns column_1, column_2
Parent Key Database Name dev
       Parent Table Name table_2
         Parent Index ID 5
      Parent Key Columns column_3, column_4

Example: Temporal UNIQUE Constraints

This example uses the following table definition.

     CREATE TABLE temporal.tab_1 (
       col_1  VARCHAR(10),
       col_2  INTEGER NOT NULL,
       col_3  INTEGER NOT NULL,
       vt_col PERIOD(DATE) AS VALIDTIME,
     CONSTRAINT vt_uniq_1 NONSEQUENCED VALIDTIME UNIQUE(col_2),
     CONSTRAINT vt_uniq_2 UNIQUE (col_3))
     PRIMARY INDEX (col_1);

The following is the output of a HELP CONSTRAINT on this table using BTEQ.

     HELP CONSTRAINT temporal.tab1.vtuniq1;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
                                 Name vt_uniq_1
                                 Type SEQUENCED VALIDTIME UNIQUE
                              Unique? Y
                             Index Id 4
                         Column Names col_2

Example: Temporal UNIQUE Constraints

This example uses the following table definition.

     CREATE TABLE temporal.tab_1 (
       col_1  VARCHAR(10),
       col_2  INTEGER NOT NULL,
       col_3  INTEGER NOT NULL,
       vt_col PERIOD(DATE) AS VALIDTIME,
     CONSTRAINT vt_uniq_1 NONSEQUENCED VALIDTIME UNIQUE(col_2),
     CONSTRAINT vt_uniq_2 UNIQUE (col_3))
     PRIMARY INDEX (col_1);

The following is the output of a HELP CONSTRAINT on this table using BTEQ.

     HELP CONSTRAINT temporal.tab1.vtuniq1;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
                                 Name vt_uniq_1
                                 Type SEQUENCED VALIDTIME UNIQUE
                              Unique? Y
                             Index Id 4
                         Column Names col_2