Explain different update operations on relational databases. How do basic operations deal with constraint violation?
Update Operations in Relational Databases
In a relational database, update operations are used to modify the data in tables. These include:
1. Insert
- Adds a new tuple (row) to a relation (table).
Example:
INSERT INTO Student (RollNo, Name, Age) VALUES (101, 'Kushal', 21);
2. Delete
- Removes one or more existing tuples from a relation.
Example:
DELETE FROM Student WHERE RollNo = 101;
3. Update (Modify)
- Changes values of one or more attributes in existing tuples.
Example:
UPDATE Student SET Age = 22 WHERE RollNo = 101;
How Update Operations Deal with Constraint Violations
Each update operation is checked against relational constraints to ensure data integrity. Here’s how:
1. Insert Operation – Violations
- Domain constraint: Value type must match the column data type.
- Key constraint: Cannot insert a row with a duplicate primary key.
- Entity integrity: Primary key cannot be null.
- Referential integrity: Foreign key must match an existing primary key in the referenced table.
If any constraint is violated, the insert is rejected.
2. Delete Operation – Violations
- Referential integrity: If a row is referenced by a foreign key in another table, deletion may be blocked.
Solutions:
- Restrict: Block the deletion.
- Cascade: Delete the dependent rows as well.
- Set Null: Set the foreign key in referencing rows to
NULL.
3. Update Operation – Violations
- Domain constraint: New value must be of valid type and range.
- Key constraint: New value must not duplicate existing key values.
- Referential integrity:
- If a foreign key is changed, the new value must still reference a valid row.
- If a referenced primary key is updated, appropriate action must be defined for dependent foreign keys (restrict, cascade, etc.).
If any rule is broken, the update is rejected unless a predefined action handles the violation.
Summary Table
| Operation | Possible Violations | Action Taken |
|---|---|---|
| Insert | Domain, Primary Key, Foreign Key, Null | Reject if any constraint fails |
| Delete | Referential Integrity | Restrict / Cascade / Set Null |
| Update | Domain, Key, Referential Integrity | Reject or handle via rules |