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 |
