17.10 - Example: SHOW For a SELECT Request on a Table With Multiple Referential Integrity Constraints - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

Suppose you have three base tables, two of which are parents to the third by means of referential integrity relationships.

In the following set of DDL table definitions, tables t1 and t3 are both parents to table t2.

     CREATE SET TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
     FOREIGN KEY (b1) REFERENCES WITH CHECK OPTION t3(a3))
     UNIQUE PRIMARY INDEX (a1);
     CREATE SET TABLE t3 (
       a3 INTEGER,
       b3 INTEGER,
     FOREIGN KEY (b3) REFERENCES WITH CHECK OPTION t1(a1))
     UNIQUE PRIMARY INDEX (a3);
     CREATE SET TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       b3 INTEGER,
     FOREIGN KEY (a2) REFERENCES WITH CHECK OPTION t1 (a1),
     FOREIGN KEY (b3) REFERENCES WITH CHECK OPTION t3 (a3))
     PRIMARY INDEX (a2);

You decide to run a SHOW report on the following query. Notice that even though the query only touches table t2 directly, the report also displays the DDL for the two parent tables of t2. t1 and t3.

     SHOW SELECT *
          FROM t2;
     *** Text of DDL statement returned.
     *** Total elapsed time was 1 second.
     -------------------------------------------------------------
     CREATE SET TABLE TEST.T2 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
           (a2 INTEGER,
            b2 INTEGER,
            b3 INTEGER, 
     FOREIGN KEY ( a2 ) REFERENCES WITH CHECK OPTION TEST.T1 ( a1 ),
     FOREIGN KEY ( b3 ) REFERENCES WITH CHECK OPTION TEST.T3 ( a3 ))
     PRIMARY INDEX ( a2 );
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE SET TABLE TEST.T1, NO FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (a1 INTEGER,
           b1 INTEGER
     FOREIGN KEY (b1) REFERENCES WITH CHECK OPTION t3(a3))
     UNIQUE PRIMARY INDEX (a1);
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE SET TABLE TEST.T3, NO FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
           (a3 INTEGER,
            b3 INTEGER
     FOREIGN KEY (b3) REFERENCES WITH CHECK OPTION t1(a1))
     UNIQUE PRIMARY INDEX (a3);