15.10 - Example: SHOW For a SELECT Request on a Table With Multiple Referential Integrity Constraints - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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);