FOR FREE YEAR SOLVED

Actions upon Referential Integrity violations 

(Important for examination)

(i) Insertion:

In the case of new insertion, it can affect the referential constraint.

Example: If we consider the above example we cannot insert any value of D_no of Employee table which value is not present at D_no of Department table.

Like we cannot insert 10 at D_no of Employee table because 10 is not present at D_no of Department table.

We can say D_no (foreign key) of the Employee table is always a subset of the D_no (primary key) of the Department table. 

(ii) Deletion:

Delete operation can violate the referential constraint.

If we consider the above example we cannot delete any value of D_no (primary key) of the Department table because it is referencing all values of D_no (foreign key) of the Employee table.

Like if we want to delete 2 from D_no (primary key) of the Department table, it will give an error “Referential integrity constraint violate” because the Department table already references value 2  at D_no. of Employee table.

(iii) Update:

Update at referenced relation may violets referential integrity constraint.

In the above relation, if we want to update any value of D_no (primary key) of the Department table, it will show the error “Referential integrity constraint violate”, if this particular value is referencing at D_no (foreign key) of the Employee table.

Like if we want to update 3 from D_no (primary key) of the Department table, it will give an error “Referential integrity constraint violate” because the Department table already references value 3  at D_no. of Employee table.

Now we can solve this referential integrity constraint violation in three ways:

Either we reject it or we cascade it, cascade means delete all values of reference table like the employee who join with this referral value (F.K) for example delete all row of employee table whose D_no is 1, then delete D_no1 from department table, but we can’t do that because if we do it delete all employee-related with this department, but we not sack the employee we can transfer to other departments. In that case, we set NULL to D_no at the employee table of the corresponding employee.

So, case to case we can take action.

Let take another example:

In that case, we use cascading because if want to delete emp_no1 from employee delete (he left the company) then definitely we delete all row dependents table who depends on emp_no1.

So, as per the case, we decide what action we take.