Update Operations and Dealing with Constraint Violations –
The Update 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 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 –- Reject the updation – (ON UPDATE NO ACTION) –
It prevents updating a parent when there are children. It is the the Default Constraint.
For example,
- 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.
- 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.
- Reject the updation – (ON UPDATE NO ACTION) –
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
- which suitable example explain how update operation are deal with constant violation
- how basic operations deal with constraint violations
- update operation deal with constraint violation
- how update operation are dealt with constant violation
- update example operation from dbms
- update operatins in dbms