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