Teradata: Referential Integrity

Pradeep

Teradata: Referential Integrity

Referential Integrity(RI) can be used to maintain data integrity in database.
Relationship(RI) can be defined between multiple tables using Primary key & Foreign Key.
This mean that row with value of particular column(Foreign Key) in table 1 can only be inserted if that value is present in table 2 (Parent table).
If RI is defined then Teradata will not check parent table at all as long as other columns from parent table are not referred(other than referenced column) in SELECT statement.
Data type of referenced column must be same in child and parent table, otherwise compilation of child table creation will fail with below error.
```
*** Failure 3976 Incorrect referential constraint definition.
```
Popular Example: department number must be present in department table before a row can be inserted into employee table for a particular department. This can be ensured using RI.

Teradata support two ways to define Referential Integrity

Hard Referential Integrity
Soft Referential Integrity

➩ Hard Referential Integrity(RI): Hard RI is general way of defining contraint using Foreign key in a table referencing other table.

Syntax/Example:


Parent Table:
============
CREATE MULTISET TABLE tutorial_db.department_hr(
   dept_no INTEGER,
   department_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
   loc_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( dept_no );



Child Table:
============
CREATE MULTISET TABLE tutorial_db.employee_hr(
  emp_no INTEGER,
  emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
  job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
  manager_id INTEGER,
  hire_date DATE FORMAT 'YY/MM/DD',
  salary DECIMAL(18,2),
  commission DECIMAL(18,2),
  dept_no INTEGER, 
  CONSTRAINT fkey FOREIGN KEY(dept_no) REFERENCES tutorial_db.department_hr(dept_no)
   )
PRIMARY INDEX ( emp_no );

Teradata will not check parent table at all as long as other columns from parent table are not referred(other than referenced column) in SELECT statement if referential integrity is defined.
Referenced column in the parent table must be defined as unique using UPI or USI otherwise creation of child table will fail with below error.
```
*** Failure 3977 The Parent Key is not unique or not valid.
```
When constrained is defined using Foreign key on a column and if that column value is not present in parent table then database will not allow row to be inserted in child table and it will fail with below error.
```
INSERT INTO tutorial_db.employee_hr(emp_no,dept_no) VALUES(223,23);

*** Failure 2700 Referential constraint violation: invalid Foreign Key value.
```
There will always be overhead of checking related value in parent table while inserting row in child table.

➩ Soft Referential Integrity(RI): Soft RI is Teradata way of defining contraint where Teradata will assume that user will maintain integrity themselves.

Syntax/Example:


Parent Table:
============
CREATE MULTISET TABLE tutorial_db.department_sr(
      dept_no INTEGER,
      department_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      loc_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( dept_no );



Child Table:
============
CREATE MULTISET TABLE tutorial_db.employee_sr(
      emp_no INTEGER,
      emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      manager_id INTEGER,
      hire_date DATE FORMAT 'YY/MM/DD',
      salary DECIMAL(18,2),
      commission DECIMAL(18,2),
      dept_no INTEGER REFERENCES WITH NO CHECK OPTION tutorial_db.department_sr(dept_no)
)
PRIMARY INDEX ( emp_no );

It is not necessary to define referenced column as UNIQUE in the parent table for Soft RI.
When constrained is defined using soft RI then Teradata will assume that user will maintain the integrity and will allow the rows to be inserted in child table even column value in not present in parent table.

If you notice in the below query, even though dept_no 300 is not present in department_sr table but query return that record as well. This happened because soft RI is defined.
Teradata is assuming that user had maintained the integrity and therefore Teradata is not bothering to reference the department table at all.


INSERT INTO tutorial_db.department_sr valuES(100, 'ACCOUNTS', 'JAIPUR');
INSERT INTO tutorial_db.department_sr valuES(200, 'R & D', 'NEW DELHI');

INSERT INTO tutorial_db.employee_sr values( 1000245, 'PRADEEP', 'PRESIDENT', null, '1981-11-17', 5000, null, 100);
INSERT INTO tutorial_db.employee_sr values( 1000258, 'BLAKE', 'SENIOR MANAGER', 1000245, '1981-05-01', 2850, null, 300);


SELECT emp_no,emp_name,salary, tutorial_db.employee_sr.dept_no, tutorial_db.department_sr.dept_no    
FROM tutorial_db.employee_sr 
INNER JOIN tutorial_db.department_sr ON (tutorial_db.department_sr.dept_no=tutorial_db.employee_sr.dept_no);

     emp_no  emp_name     salary      dept_no     dept_no
-----------  --------- ---------  ----------- -----------
    1000258  BLAKE       2850.00          300         300
    1000245  PRADEEP     5000.00          100         100



Explain plan: Department_sr table is not referred by Teradata while fetching data.
Explanation
--------------------------------------------------------------------------------
  1) First, we lock a distinct tutorial_db."pseudo table" for read on a
     RowHash to prevent global deadlock for tutorial_db.employee_sr.
  2) Next, we lock tutorial_db.employee_sr for read.
  3) We do an all-AMPs RETRIEVE step from tutorial_db.employee_sr by
     way of an all-rows scan with a condition of ("NOT
     (tutorial_db.employee_sr.dept_no IS NULL)") into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with low confidence to be 2 rows (248 bytes).
     The estimated time for this step is 0.04 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.04 seconds.

➩ Hard vs Soft Referential Integrity

	Hard Referential Integrity	Soft Referential Integrity
Referenced Column	Must be defined as Unique in parent table.	Not necessary to be defined as Unique in parent table.
System Overhead	System always have to check referenced column value in parent table.	System will not check referenced column value in parent table. No system overhead
Onus for Maintaining Integrity	System will maintain by itself and throw error if there is any violation.	Teradata will assume that users will ensure integrity of data between parent & child table.