Database Operations and Dealing with Constraint Violations- The Delete Operation

The Delete Operation :

Consider two existing relations named EMPLOYEE and DEPARTMENT.

Insert - Database Operations and Dealing with Constraint Violations

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.

    Deletion in a Referencing Relation - Database Operations and Dealing with Constraint Violations
  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.
      on delete no action - Database Operations and Dealing with Constraint Violations
    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>
        On delete cascade - Database Operations and Dealing with Constraint Violations
    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 :
      on delete set null - Database Operations and Dealing with Constraint Violations

Click on the Questions on Constraint Violations or Foreign Key


Previous Home Next
Constraint Operations during
Insert Operation
Constraint Violations During
Update Operation

     

Incoming search terms:

  • constraint violation in dbms
  • explain with example the diffrent constrant that are violated during update and delete operations
  • DatabSe constraint violation in deletion
  • insert update deelete constaraits edugrabs

Leave a Reply