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

Constraints in Teradata - With detailed example and work logs and Syntax

Constraints can be grouped as Column level and Table level constraints.

Column constraints apply to single columns as a part of the column definition. Column constraints include:

•CHECK constraint definition clause on a single column

•PRIMARY KEY constraint definition clause on a single column

•REFERENCES constraint definition clause on a single column

•UNIQUE constraint definition clause

Table constraints apply to multiple columns. Table-level constraints include:

•CHECK constraint definition clause on multiple columns

•REFERENCES constraint definition clause on multiple columns

•PRIMARY KEY constraint definition clause on multiple columns

•UNIQUE constraint definition clause on multiple columns

•FOREIGN KEY constraint definition clause

FOREIGN KEY constraint definitions must also specify a REFERENCES clause.

Please go through the below work log to understand more about constraints in Teradata.

/* CONSTRAINTS IN TERADATA */

database financial;

/* The base table should exists and a primary key should be defined already */

/* Only then Foreign key/referential integrity creation will be allowed */

create table custonline_constraint_1

(

cust_id integer not null primary key

) ;

/* Add the integrity constraints on the child table */

drop table constraint_test;

create multiset table constraint_test

(

cust1_id integer not null unique check (cust1_id > 0)

, cust_name varchar(50)

, c_id integer references custonline_constraint_1(cust_id)

,check (char_length(cust_name) > 1)

)

primary index(cust1_id, cust_name);

/* The below insert will give "CHECK CONSTRAINT VIOLATION" as the value we are inserting for cust1_id is 0

 * The check constraint allows value which are greater than zero

 */

insert into constraint_test values(0,'prabhu', 11);

/* we will need to have the foreign key value for c_id column in the parent table (custonline_constraint_1)

 * if the value doesnt exists, we will get an error "REFERENTAIL CONSTRAINT VIOLATION"

 */

insert into constraint_test values(1,'prabhu', 11);

/* Let us now check the with check option of views

 * If any row is inserted in the table which will not be displayed in the view, then those rows are discarded

 * STEP-1 will be to insert records in the base table custonline_constraint_1

*   STEP-2 will insert the FOREIGN KEY into the table constraint_test

 */

insert into custonline_constraint_1 values(11);

insert into constraint_test values(1,'prabhu', 11);

/* Create the view with check option

 * Then try to insert rows having cust_id > 10 using the view

 */

replace view v_constraint_test as select * from constraint_test where cust1_id < 10 with check option;

insert into v_constraint_test values(13,'prabhu', 11);

/* If you insert rows directly into the table, the rows with cust1_id > 10 will be inserted

 * The check constraint only restricsts inserts or updates using the view

 */

insert into constraint_test values(13,'prabhu', 11);

/* The below insert statement fails as there is check constraint that requires values of cust_name

 * to have length larger than one character

 */

insert into constraint_test values(12,'A', 11);

--- successfully inserts 1 row into the table

select * from v_constraint_test;

Please go through the above queries to understand each concept. 

The next blog will contain a few additional implementation of constraints.