-- 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.

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