DBMS - Handling Constraint Violations

Relational database systems are not just about storing data; they also need to focus on maintaining the integrity and consistency of data during various operations.

In this chapter, we will take a close look at update operations, along with the concept of transactions, and strategies for handling constraint violations, with detailed examples for a better understanding.

Insert, Delete, and Update Operations

There are three major operations that we normally perform on a database –

• Insert − Adds new tuples to a table.
• Delete − Removes tuples from a table.
• Update (or Modify) − Changes the values in existing tuples.

The update operation in a database allows modifications to the existing values of one or more attributes within a relation. The goal is to maintain a consistent state that adheres to all integrity constraints.

All the three operations have the potential to breach the constraints defined in the database schema. Constraints such as key constraints, entity integrity, referential integrity, etc., are used for preserving data quality.

Operations and Constraint Violations

The insert operation is used to add a new record to a relation. However, it can trigger several constraint violations:

• Domain Constraint − If the data type or range of a value in an attribute is incorrect, this constraint is breached.
• Key Constraint − Violated when a primary key value in the new tuple matches an existing primary key.
• Entity Integrity − Cause error if a primary key in the new tuple contains a NULL value.
• Referential Integrity − Occurs when a foreign key references a non-existent value in the related table.

Consider we are attempting to insert the tuple:

<'Alicia', 'J', 'Zelaya', '999887777', '1960-04-05', 
   '6357 Windy Lane, Katy, TX', F, 28000, '987654321', 4>

into the EMPLOYEE table. In this case, a key constraint violation will occur if 999887777 already exists as a primary key.

The delete operation can violate referential integrity because the tuple being removed is referenced by foreign keys in other tables. For instance, deleting an employee whose ID is used in the WORKS_ON table would create orphaned records. This will disrupt data relationships. For example, deleting an EMPLOYEE with Ssn = '333445555' could violate referential integrity because tuples in DEPARTMENT, WORKS_ON, and DEPENDENT might reference this employee.

Now, let's check what kind of constraint violations might occur while updating a database. Updating the existing data can be complex, especially when altering primary keys or foreign keys. Modifying a primary key is equivalent to deleting the original record and inserting a new one, which may trigger key constraints or referential integrity issues. For example, updating the Dno (department number) of an EMPLOYEE record to a value that does not exist in the DEPARTMENT table results in a referential integrity violation.

Handling Constraint Violations

In this section, let's understand how to handle the constraint violations that occur during the insert, delete, and update operations.

Handling Insertion Violations

When an insertion violates constraints, the most common response is to reject the insertion and notify the user. But, there are situations where DBMS can assist by suggesting corrective measures:

• Prompting for valid values − The DBMS might ask for a non-NULL value for primary keys if the violation is due to NULL entries.
• Proposing related tuple insertion − If referential integrity fails, the system may suggest inserting a corresponding record into the referenced table.

For example, attempting to insert an EMPLOYEE with a Dno value that does not exist can prompt the DBMS to suggest inserting a valid department first.

Handling Deletion Violations

Delete operations may also cause problems, primarily affecting referential integrity. The strategies to manage these violations are:

• Restrict − Prevents the deletion if it disrupts related records.
• Cascade − Automatically deletes all related tuples. This is useful when removing an entry that is referenced in multiple tables.
• Set NULL / Default − Changes foreign key references to NULL or a preset default value when the primary key is deleted.

For example, if an EMPLOYEE tuple with Ssn = '123456789' is deleted, then the DBMS may set the Essn in WORKS_ON to NULL. This will avoid referential integrity breaches.

Managing Update Operation

Update operations can also breach constraints, particularly when modifying attributes tied to keys:

• Updating a primary key − The DBMS treats it like a combination of delete and insert. If other tables reference the original primary key, this may lead to referential integrity problems.
• Updating a foreign key − The DBMS must verify that the new value references an existing primary key or set it to NULL.

For example, changing an employee's Ssn from '123456789' to '987654321' may lead to issues if '987654321' is already in use as a primary key elsewhere.

Transactions and Their Importance

Along with database operations, another important concept is transactions. In a database context, a transaction is an atomic sequence of operations that includes reading or updating the data. Transactions ensure that operations are performed as a single unit, maintaining the database's consistency.

Key properties of transactions include:

• Atomicity − Ensures that all parts of a transaction are completed; otherwise, none are applied.
• Consistency − Keeps the database in a valid state.
• Isolation − Transactions do not interfere with each other.
• Durability − Once a transaction is committed, changes are permanent.

For example, a bank transfer operation where one account is debited, and another is credited must either complete fully or revert to the initial state to prevent inconsistencies.

Handling Transactions in Practice

Transactions are nothing but a set of rules that follow certain behavior. Transactions often involve multiple steps that could individually violate constraints. Atomicity means the DBMS checks for violations at each step:

• Before committing − The DBMS verifies all constraints. If a violation is detected, the entire transaction rolls back to prevent partial changes.
• After a rollback − The database state returns to what it was before the transaction began, preserving data consistency.

For example, in an order processing system, a transaction might include checking stock availability, updating inventory, and recording the sale. If the stock update fails due to an inventory constraint, the entire transaction should revert.

Referential Integrity Violation

Let's check a practical scenario of constraint violation. Consider the following UPDATE operation:

UPDATE EMPLOYEE SET Dno = 10 WHERE Ssn = '999887777';

If Dnumber = 10 does not exist in DEPARTMENT, then this update fails due to a referential integrity violation. The DBMS must check to ensure that relationships remain consistent.

Combining Strategies for Complex Violations

In some cases, a combination of cascade, set NULL, and restrict strategies might be applied based on specific needs:

• Cascade for deep deletions − When deleting a department, cascade deletes associated employees and projects.
• Set NULL for non-critical references − Updates foreign key values to NULL when the primary record is removed.
• Restrict for critical data − Prevents deletion if essential references are present, such as financial records.

Conclusion

In this chapter, we explained how update operations, transactions, and constraint handling are integral to maintaining data integrity in relational databases. We understood the importance of managing insert, delete, and update operations without violating any constraints. We also explored how the DBMS reacts to constraint violations with strategies like restrict, cascade, and set NULL. Additionally, we examined how transactions help ensure that changes maintain consistency and follow atomicity.