First of all we consider the two relation :
A) Referenced relation R having primary key A, (say)
B) Referencing relation S having foreign key B , (say) referencing to the relation R's primary key A.
Now first of all deletion of a tuple in referencing relation which is S here will not cause any problem as no update is required and all other tuples of S are still referencing to R..Hence no referential integrity violation occurs in case a tuple from referencing relation is deleted.
But problem may come when we delete a tuple from referenced relation In that case we have a tuple in R which has 3 attributes A,D and E with A as primary key like this : { 1 , 2 , 3 } which is being deleted.
Now say in the referencing relation also we have 3 attributes B , F and G with B being the primary key and say we have the tuples : { 1 , 4 , 5 }
{ 1 , 5 , 6 }
Now we can see that in both of these the foreign key value {1} is still present even though the primary key value in R which is {1} is deleted .So this is clearly an inconsistency and needs to be avoided.So we have 3 ways to do this.Let us discuss them one by one:
A) ON DELETE NO ACTION :
Deletion from the referenced relation which is R here is restricted if referential integrity violation occurs.So here what we do is we delete the corresponding tuples in the referencing relation first which are in this case :
{1 , 4 , 5} and {1 , 5 , 6} of S which is deleted first after which only the corresponding tuple of referenced relation which is {1 , 2 , 3} of R can be deleted.
B) ON DELETE CASCADE :
As the phrase suggests , if user has requested the deletion of referenced relation tuples , then we delete related records from referencing relation along with it as well.So it is less restrictive action than previous one since we are allowing the deletion of referenced relation always but with the constraint that the corresponding records/tuples of referencing relation is also deleted along with.
C) ON DELETE SET NULL :
Here on deletion of referenced relation tuple , we set NULL value in the corresponding foreign key attribute of the referencing relation only and leave remaining attribute values of the referencing relation unchanged.But here also , say , if we have the declaration which specifies that foreign key is a NONNULL attribute then we cannot set NULL to it.In that case , it becomes equivalent to "ON DELETE NO ACTION"
Thus here on deletion of tuple {1,2,3} from R , the modification required in the referencing relation tuples is :
{NULL , 4 ,5 }
and {NULL , 5 , 6 }
provided B which is the foreign key is not declared "NOT NULL" else this action is not allowed and scenario becomes similar to "ON DELETE NO ACTION".
Hope this small illustration helps you..
