Complete introduction to referential integrity in Teradata - Example script and explanation

DATABASE RETAIL; 

DROP TABLE ORDERS;

/* Creation of the Parent Table  ORDERS */

CREATE MULTISET TABLE ORDERS

(

ORDER_ID INTEGER NOT NULL PRIMARY KEY

, ORDER_NAME VARCHAR(30) CHECK(CHAR_LENGTH(ORDER_NAME) > 1)

, ORDER_DT DATE FORMAT 'YYYY-MM-DD'

---, PRIMARY KEY(ORDER_ID)

)

PRIMARY INDEX(ORDER_NAME)

;

/* TESTING FOR THE CHECK CONSTRAINT */

INSERT INTO ORDERS (1,'PENCIL','2015-08-10');

INSERT INTO ORDERS (2,'PEN','2015-08-10');

/* CREATE A CHILD TABLE WITH REFERENTIAL INTEGRITY ON ORDERS TABLE */

DROP TABLE TRANSACT;

CREATE MULTISET TABLE TRANSACT

(

TRANSACT_ID INTEGER

, ORDER_ID INTEGER REFERENCES RETAIL.ORDERS

, TRANSACT_DATE DATE FORMAT 'YYYY-MM-DD'

, POS_NUMBER DECIMAL(18,0) NOT NULL UNIQUE

)

PRIMARY INDEX(TRANSACT_ID);

/* TRY INSERT VALUES WHICH ARE NOT PRESENT IN THE ORDERS TABLE */

INSERT INTO TRANSACT(1, 3, '2015-09-10', 1);

--- SINCE 3 IS NOT PRESENT IN THE ORDERS PARENT TABLE, THIS IS GIVING FOREIGN KEY ERROR

INSERT INTO TRANSACT(1, 1, '2015-09-10', 1);

INSERT INTO TRANSACT(1, 1, '2015-09-10', 2);

INSERT INTO TRANSACT(3, 1, '2015-09-10', 1);

Once the Child table is created, we will not be able to ALTER the DDL of the PARENT table. 

As seen below , we will get an error if we run the Drop table statement.

/* THE BELOW QUERY WILL GIVE YOU AN ERROR AS ITS REFERNCED BY A CHILD TABLE */

DROP TABLE RETAIL.ORDERS;

ALTER table in Teradata will fail once we have created CHILD tables

Since, we want to find out the child tables for the ORDERS table, we can query the ALL_RI_Parents metadata tables. To edit a Parent table, we will have to drop the child tables also.

/* MODIFY THE STRUCTURE OF A PARENT TABLE

 * THEN THE BELOW QUERY CAN BE USED */

SELECT * FROM DBC.All_RI_Parents;

parent and child tables in teradata ALL_RI_PARENTS metadata table

Understand how partitioning improves query performance using Teradata EXPLAIN

Today, we will see if adding a partition-by clause improves our SELECT queries in Teradata.

Let us go through the example below:

DATABASE RETAIL;

SELECT * FROM CUSTOMER;


ALTER TABLE CUSTOMER ADD JOINING_DT DATE FORMAT 'YYYY-MM-DD';

UPDATE CUSTOMER SET JOINING_DT = '2015-02-01' WHERE CUST_ID = 1;
UPDATE CUSTOMER SET JOINING_DT = '2015-05-01' WHERE CUST_ID = 201;

/* lET US RUN A SELECT FOR A PARTICULAR MONTH OF THE YEAR */

SELECT * FROM CUSTOMER WHERE JOINING_DT BETWEEN '2015-01-01' AND '2015-01-31';

/* "  1) First, we lock a distinct RETAIL.""pseudo table"" for read on a"
     RowHash to prevent global deadlock for RETAIL.CUSTOMER.
"  2) Next, we lock RETAIL.CUSTOMER for read."
  3) We do an all-AMPs RETRIEVE step from RETAIL.CUSTOMER by way of an
     all-rows scan with a condition of ("(RETAIL.CUSTOMER.JOINING_DT <=
     DATE '2015-01-31') AND (RETAIL.CUSTOMER.JOINING_DT >= DATE
"     '2015-01-01')"") into Spool 1 (group_amps), which is built locally"
     on the AMPs.  The size of Spool 1 is estimated with no confidence
     to be 2 rows (102 bytes).  The estimated time for this step is
     0.03 seconds.
*/

As we see in STEP-3, the SELECT with range based WHERE results in a Full Table Scan. This is not a great way to query large tables.

Let us explore the alternate approach.

CREATE TABLE CUSTOMER_1
(
CUST_ID INTEGER
,CUST_NAME VARCHAR(30)
,JOINING_DT DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX(JOINING_DT)
PARTITION BY (RANGE_N(
JOINING_DT BETWEEN '2014-01-01' AND '2014-12-31' EACH INTERVAL '1' YEAR
,'2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH
, NO RANGE OR UNKNOWN))
;

We insert the same data as was present in Customer table.

INSERT INTO CUSTOMER_1 SELECT * FROM CUSTOMER;

SELECT * FROM CUSTOMER_1 WHERE JOINING_DT BETWEEN '2015-01-01' AND '2015-01-31';

/* IF WE RERUN THE QUERY ON CUSTOMER TABLE ON THE NEW PARTIIONED TABLE THE EXPLAIN CHANGES AS BELOW

"  1) First, we lock a distinct RETAIL.""pseudo table"" for read on a"
     RowHash to prevent global deadlock for RETAIL.CUSTOMER_1.
"  2) Next, we lock RETAIL.CUSTOMER_1 for read."
  3) We do an all-AMPs RETRIEVE step from a single partition of
     RETAIL.CUSTOMER_1 with a condition of (
     "(RETAIL.CUSTOMER_1.JOINING_DT <= DATE '2015-01-31') AND
     (RETAIL.CUSTOMER_1.JOINING_DT >= DATE '2015-01-01')") into Spool 1
"     (group_amps), which is built locally on the AMPs.  The size of"
     Spool 1 is estimated with low confidence to be 4 rows (204 bytes).
     The estimated time for this step is 0.03 seconds.

*/

As we see now that the partition is leveraged when the partition column is used in the WHERE clause of a SELECT query. This results in faster query execution.

Join us in the classroom for more stimulating sessions on Teradata.