Soft RI in Teradata

Teradata has the concept of “Soft RI”. In this concept, a foreign key is created but its restriction is not enforced.
What happens in that situation?

Let’s look at the normal situation of referential integrity. Suppose, we have two tables. One table is referred to by a second table. If a foreign key is created, we have a limitation on which records we may insert into the table under normal referential integrity. We cannot insert records that have a foreign key for which no relational record exist in the referring table. That is the normal foreign integrity constraint.
Example: we have an “employee” table that has a foreign key “dept_no” that refers to a record in a “department” table. Suppose the department table has records for department 101, 102 and 103. If a foreign key is created, one cannot add records in the employee table that refer to department 104, as this doesn’t exist in the department table.

To enforce this referential integrity, each insert must be followed by a check as to whether the inserted record complies to the referring table. This check costs time. Hence an insert incurs additional processing time.

In a data warehouse environment, this overhead may be prohibitive. Moreover it might not be necessary as we retrieve the records from a source that has already enforced the referential integrity.

In that situation, we may apply “Soft RI”. In that case a foreign key relationship is created but its referential integrity is not enforced during loading.
In that case, we avoid the costs of the check of referential integrity. This leads to more records that can be loaded in a given time frame.

DROP TABLE SAN_D_FAAPOC_01.EMPLOYEE;
DROP TABLE SAN_D_FAAPOC_01.DEPARTMENT;


CREATE SET TABLE SAN_D_FAAPOC_01.DEPARTMENT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      DEPT_NO INTEGER NOT NULL,
      DEPT_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      DEPT_LOC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( DEPT_NO );



INSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (101,'SALES','MUMBAI');
INSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (102,'ACCOUNTS','MUMBAI');
INSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (103,'HUMAN RESOURCES','MUMBAI');


CREATE  TABLE SAN_D_FAAPOC_01.EMPLOYEE
(
EMP_NUM INTEGER NOT NULL,
EMP_NAME CHAR(30) NOT NULL,
DEPT_NO INTEGER NOT NULL
,CONSTRAINT FOREIGN_EMP_DEPT FOREIGN KEY ( DEPT_NO)  REFERENCES  WITH NO CHECK OPTION DEPARTMENT(DEPT_NO)   
)
UNIQUE PRIMARY INDEX(EMP_NUM);


INSERT INTO SAN_D_FAAPOC_01.EMPLOYEE VALUES (123456,'VINAY',101);
INSERT INTO SAN_D_FAAPOC_01.EMPLOYEE VALUES (123457,'SACHIN',104);

SEL
DEPT.DEPT_NO,
EMP.EMP_NUM,
EMP.EMP_NAME
FROM
SAN_D_FAAPOC_01.EMPLOYEE EMP
INNER JOIN   ----> HERE INNER JOIN DOES SOFT REFERENTIAL INTEGRITY & PICKS ONLY MATCHING COLUMNS
SAN_D_FAAPOC_01.DEPARTMENT DEPT
ON
DEPT.DEPT_NO = EMP.DEPT_NO;