difference between soft referential integrity and hard referential integrity in teradata

Training

difference between soft referential integrity and hard referential integrity in teradata

This metirial Provided by www.olineinformaticatrainig.com/online-teradata-trainig

Hard Referential Integrity -

It involves manual creation of Referential links like "foreign key ... references"....  Please find an example below between department & employee table .

-- create department table & employee table and have referential integrity set explicity  between them as follows

Create table department

(

Dept_no integer not null,

Dept_name varchar(30),

Dept_loc varchar(50)

)

Unique primary index(dept_no);

--- insert some sample records into  Department table

insert into department values (101,'Sales','mumbai);

insert into department values (102,'Accounts','mumbai);

insert into department values (103,'Human Resources','mumbai);

Create  table Employee

(

Emp_num integer not null,

Emp_name carchar(30) not null,

Dept_no integer not null,

Constraint foreign_emp_dept foreign key ( Dept_no)  references department(dept_no)

)

unique primary index(emp_num);

Now , try to insert following record into employee table as below

insert into Employee values (123456,'swapnil',104);

-- following statement fails because  employee table refers to dep_no column in department table to check for value 104.  Since it wont be able to find the value it will fail with referential integrity error.

This type of referential integrity is called Hard referential integrity.

SOFT referential integrity.

This is handled at system level by optimizer  and no need to specify references details.

Create table department

(

Dept_no integer not null,

Dept_name varchar(30),

Dept_loc varchar(50)

)

Unique primary index(dept_no);

--- insert some sample records into  Department table

insert into department values (101,'Sales','mumbai);

insert into department values (102,'Accounts','mumbai);

insert into department values (103,'Human Resources','mumbai);

Create  table Employee

(

Emp_num integer not null,

Emp_name carchar(30) not null,

Dept_no integer not null

--,Constraint foreign_emp_dept foreign key ( Dept_no)  references department(dept_no)  

-- here constraint is not used/commented

)

unique primary index(emp_num);

insert into Employee values (123456,'vinay',101);

insert into Employee values (123456,'Sachin',104);

We can achieve soft  referential integrity by using  inner join between department & Employee tables

sel

dept.dept_no,

emp.emp_num,

emp.emp_name

from

employee emp

inner join   ----> here inner join does soft referential integrity & picks only matching columns

department dept

on

dept.dept_no = emp.dept_no;

advantages of Soft RI over Hard RI is that there is  that there is no overhead of maintaining Referential integrity tables in case of Soft RI & it would help in better performance in case of tables with large values .

Soft referential integrity is ideal in case of large datawarehhouses becuase of ETL mechanisms which cleanse unnecessary records .Soft RI is suggested on data warehouses because amount of processing required for referential integrity can add to overload of  Data warehouse system

For more information regarding Teradata Trainig visit us http://www.onlineinformaticatraining.com/training/

we are Giving you a best trainig with experianced software proffesionals with real time project explaination

3 REPLIES

Re: difference between soft referential integrity and hard referential integrity in teradata

hi 

From the above example in soft RI there is no integrity between the tables so why it is called soft integrity ?

Teradata Employee

Re: difference between soft referential integrity and hard referential integrity in teradata

Hi, 

in the example above, the soft RI is not defined.    The phrase "-- here constraint is not used/commented" is not defining soft RI.   

If there is no integrity in the data, then soft RI should NOT be defined.  

Regards,

Vlad.

Re: difference between soft referential integrity and hard referential integrity in teradata

Constraint foreign_emp_dept foreign key ( Dept_no)  references WITH NO CHECK OPTION department(dept_no)  

with no check option enable the soft RI, it is eanbled when only  tables are joined, it is disabled when loading the data 

for detailed example please refer this article

http://www.teradatahelp.com/2012/02/difference-between-soft-referential.html