Teradata has the concept of “Soft RI”. In this concept, a foreign key is created but its restriction is not enforced.
\nWhat happens in that situation?<\/p>\n
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.
\nExample: 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.
\nIn 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.<\/p>\n
\nDROP TABLE SAN_D_FAAPOC_01.EMPLOYEE;\nDROP TABLE SAN_D_FAAPOC_01.DEPARTMENT;\n\n\nCREATE SET TABLE SAN_D_FAAPOC_01.DEPARTMENT ,NO FALLBACK ,\n NO BEFORE JOURNAL,\n NO AFTER JOURNAL,\n CHECKSUM = DEFAULT,\n DEFAULT MERGEBLOCKRATIO\n (\n DEPT_NO INTEGER NOT NULL,\n DEPT_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,\n DEPT_LOC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)\nUNIQUE PRIMARY INDEX ( DEPT_NO );\n\n\n\nINSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (101,'SALES','MUMBAI');\nINSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (102,'ACCOUNTS','MUMBAI');\nINSERT INTO SAN_D_FAAPOC_01.DEPARTMENT VALUES (103,'HUMAN RESOURCES','MUMBAI');\n\n\nCREATE TABLE SAN_D_FAAPOC_01.EMPLOYEE\n(\nEMP_NUM INTEGER NOT NULL,\nEMP_NAME CHAR(30) NOT NULL,\nDEPT_NO INTEGER NOT NULL\n,CONSTRAINT FOREIGN_EMP_DEPT FOREIGN KEY ( DEPT_NO) REFERENCES WITH NO CHECK OPTION DEPARTMENT(DEPT_NO) \n)\nUNIQUE PRIMARY INDEX(EMP_NUM);\n\n\nINSERT INTO SAN_D_FAAPOC_01.EMPLOYEE VALUES (123456,'VINAY',101);\nINSERT INTO SAN_D_FAAPOC_01.EMPLOYEE VALUES (123457,'SACHIN',104);\n\nSEL\nDEPT.DEPT_NO,\nEMP.EMP_NUM,\nEMP.EMP_NAME\nFROM\nSAN_D_FAAPOC_01.EMPLOYEE EMP\nINNER JOIN ----> HERE INNER JOIN DOES SOFT REFERENTIAL INTEGRITY & PICKS ONLY MATCHING COLUMNS\nSAN_D_FAAPOC_01.DEPARTMENT DEPT\nON\nDEPT.DEPT_NO = EMP.DEPT_NO;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-579","post","type-post","status-publish","format-standard","hentry","category-data-warehousing"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/579","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=579"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/579\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=579"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=579"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=579"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}