Database Operations and Dealing with Constraint Violations – The Delete Operation

Database Operations and Dealing with Constraint Violations- The Delete Operation

The Delete Operation :

Consider two existing relations named EMPLOYEE and DEPARTMENT. Some Basic Points about the Figure –

• Here, ENO is a Primary Key and DNO is a Foreign Key in EMPLOYEE relation.
• Table that contain candidate key  is called referenced relation and
• The table containing foreign key is called referencing relation.
• So, the relation DEPARTMENT is a referenced relation and
• The relation EMPLOYEE is a referencing relation.

The Delete Operation violates only referential Integrity Constraints or Integrity Rule 2.

1. Deletion in a Referencing Relation (EMPLOYEE relation)   If we delete a tuple from the referencing relation, then it causes no violation. For example, Deletion of <3, ‘Somvir’, 22, 10> from EMPLOYEE relation is allowed because deletion of this tuple causes no violation.
2. Deletion in a Referenced Relation (DEPARTMENT relation) – There are three options available if a deletion causes violation –
   1. Reject the Deletion – (ON DELETE NO ACTION) – It prevents deleting a parent when there are children. It is the the Default Constraint. For example, Deletion of <10, ‘Rohtak’> from DEPARTMENT relation is not allowed because deletion of this tuple will violate referential integrity as in the EMPLOYEE relation, the tuple with DNO=10 causes problem. And therefore, reject the deletion.
   2. Cascade Deletion – (ON DELETE CASCADE) – If deletion causes integrity violation, then delete from both the table i.e. if the tuples are deleted from the referenced table, then the tuple will also be deleted from the referencing relation that is being deleted. For example, Deletion of <10, ‘Rohtak’> from DEPARTMENT relation will delete the following tuples in EMPLOYEE relation :
      • <1, ‘Ankit’, 19, 10>
      • <3, ‘Somvir’, 22, 10>
      • <4, ‘Sourabh’, 19, 10>
   3. Modify the referencing Attributes – (ON DELETE SET NULL) – sets null value or some valid value in the foreign key field for corresponding deleting referenced value. i.e. changing the referencing attribute values that cause the violation either null or another valid value. If there is no restriction or constraint applied for putting the NULL value in the referencing relation – then allow to delete from referenced relation otherwise prohibited. For example, Deletion of <10, ‘Rohtak’> from DEPARTMENT relation will delete the following tuples in EMPLOYEE relation :
      • <1, ‘Ankit’, 19, 10>
      • <3, ‘Somvir’, 22, 10>
      • <4, ‘Sourabh’, 19, 10>
      and null values will be filled in the EMPLOYEE relation on that place. So the relations will look like this :  

]]>