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