Update Operations and Dealing with Constraint Violations

Update Operations and Dealing with Constraint Violations –

The Update Operation :

Consider two existing relations named EMPLOYEE and DEPARTMENT.

Insert - Update 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 update operation violates only referential Integrity Constraints or Integrity Rule 2.

  1. Updation in a referencing relation –
    updation of referencing attribute may causes referential integrity violation. The Updation restricted if causes violation. For Example,
    updation in referencing relation-Update Operations and Dealing with Constraint Violations
    If there is no violation, then updation will be allowed.
    allow updation in referencing relation- Update Operations and Dealing with Constraint Violations
  2. Updation in a referenced relation –
    There are again three options available if an updation causes violation –

    1. Reject the updation – (ON UPDATE NO ACTION) –
      It prevents updating a parent when there are children. It is the the Default Constraint.
      For example,
      on update no action - Update Operations and Dealing with Constraint Violations
    2. Cascade Updation – (ON UPDATE CASCADE) –
      If updation causes integrity violation, then update in both the table i.e. if the tuples are updated from the referenced table, then the tuple will also be updates from the referencing relation that is being updated.
      on update cascade - Update Operations and Dealing with Constraint Violations
    3. Modify the referencing Attributes – (ON UPDATE SET NULL) –
      sets null value or some valid value in the foreign key field for corresponding updating referenced value. i.e. changing/updating 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 update from referenced relation otherwise prohibited.
      on update sets null - Update Operations and Dealing with Constraint Violations
Previous Home Next
Constraint Violations during
DELETE Operation		 Conversion of ERD to Relational
Model or Minimisation of ERD
     

Incoming search terms:

  • update operation in dbms
  • update operations in dbms
  • update operations and dealing with constraint violations
  • dealing with constraint violation
  • update operation on relation in dbms
  • update operation in realation dbms
  • types of updatr operation in dbms
  • mention three types of update operation on a relation
  • what is update operations and constraints violations
  • Describe the different type of update operations on a relattion

Leave a Reply