Update Operations and Dealing with Constraint Violations

Update Operations and Dealing with Constraint Violations –

Consider two existing relations named EMPLOYEE and DEPARTMENT.

Some Basic Points about the Figure –

The update operation violates only referential Integrity Constraints or Integrity Rule 2.

Updation in a referencing relation –
updation of referencing attribute may causes referential integrity violation. The Updation restricted if causes violation. For Example,

If there is no violation, then updation will be allowed.
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,
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.
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.