Integrity Constraint Violation

Introduction

SQL Constraints are the rules or restrictions applied to the database to limit the type and accuracy of data entering the table. If the data to enter satisfies the constraints rule, it enters successfully.

Let's consider the table named Employee with EmpId as the primary key.

Table Employee:

Let's consider another table named Designation. It contains the list of all the available designations in the company.

Table Designation:

We will insert a new value in the table and check if they violate the constraints.

Here, you can see that the designation Business Analyst is not available in the company list. So, none of the employees can have a designation other than that present in the list. Entering any value other than what satisfies the constraints rule should lead to a violation.

Integrity constraint occurs when a foreign key references the primary key. It defines that the values of the foreign key must be present in the primary key or is NULL.

There are three primary causes of referential integrity constraint violation. They are as follows:

• Insertion of new tuples in a referencing relation in a database
• Deletion of some existing tuples from a referenced relation in the database
• Updation or changes in the values of the existing tuples in a referenced relation in the database

Applying the above modifications to the relations in the database may or may not violate the constraints of the database.

Insertion in a Referencing Relation

Insertion of a tuple in the relationship can cause violations in different ways. Let's have a look at each of them one by one.

Violation of Domain Constraint

Domain constraint in SQL restricts the values or type of attributes a column can contain. The data type of values of a domain can be string, integer, character, date, or time data type.

Let's see examples of domain constraints.

• The value inserted should always be a number for representing the age in a database. Insertion of any character, string, or another data type may cause a violation of domain constraint.
• The domain constraint not only restricts the datatype but also defines the attribute's range or set of values. For example, the domain constraint can fix the age between 10 to 50. Any number greater than 50 or less than 10 causes domain constraint violation when it is tried to be inserted.

Violation of the Entity Integrity Constraint

The entity constraint makes sure that none of the values in the primary key column is assigned NULL. The primary key identifies the individual records and connects the relations and thus cannot be NULL.

Violation of Key Constraints

Keys in DBMS are attributes or sets of attributes uniquely identifying the relation's rows. The primary key is one of the multiple keys present in the entity set that can contain unique and non-NULL values in the database. Insertion of value violates the key constraints when it is already present in an existing tuple in the table.

Violation of Referential Integrity Constraints

Values already present in the list of referenced values are allowed to insert in the referencing attribute.

Values not present in the referenced list cannot be inserted in the referencing attribute. Inserting such values causes referential integrity constraint violation.

Let's understand it using examples.

We will discuss with the help of the following two schemas:

Student ( Roll_no, Name, Age, Branch_Code ) Branch( Branch_Code, Branch_Name )

The Student schema is the referencing relation, while the Branch schema is the referenced relation. It can also be said as the Student relation references the Branch relation. Branch_Code is the foreign key in the tables.

Student

Branch

According to the constraints, any value not present in the referenced table cannot get inserted into the referencing table. For the above table, Students having Branch_Code other than the values in the Branch table cannot get inserted into the Students table. For example, a Student with Branch_Code BT ( BioTechnology ) cannot be present in the Students relation as the branch code BT is not present in the relation Branch.

Deletion from a Referenced Relation

Deletion of values from a table can only cause referential integrity constraint violation.

According to the constraints, if the referencing attribute uses a value of the referenced attribute, that row cannot get deleted from the referenced relation. Deleting such rows can cause referential integrity constraint violation.

Let's consider some examples from the above two relations that cause integrity constraint violation.

• A row having Branch_Code CSE ( Computer Science and Engineering ) cannot delete from the relation Branch. It is because referencing relation Student references the value 'CSE' by the referencing attribute Branch_Code.

The above SQL statement deletes the record of the computer science branch from the Branch table. It violates the constraints as a student is already present in the Student table with its Branch as CSE.

Student

Branch

• A row having Branch_Code ECE ( Electronics Engineering ) can be safely deleted from the relation Branch as the referencing relation Student does not reference the value 'ECE' by the referencing attribute Branch_Code.

The above SQL statement deletes the record of the Electronics Engineering branch from the Branch table. Deleting that doesn't violate the constraints as there is no record in the Student table referenced by it.

Student

Branch

Handling the Violations

The referential integrity constraint violation caused by the deletion from a referenced relation gets handled in the following three ways:

• It is by simultaneously deleting the tuples from the referencing relation where the referencing attribute has the value of the referenced attribute to be deleted. This way of constraint violation handling is known as On Delete Cascade.
• The second method is to abort or delete the deletion request of the referenced relation if the value to delete is present in the referencing attribute of the referencing relation.
• The third method involves replacing the value with NULL or some other value in the referencing relation if the referencing attribute uses the value to delete from the referenced relation.

Updation in a Referenced Relation

According to the constraints, a row of the referenced relation cannot update if the referencing attribute uses the value of the referenced attribute of the row. Updating such rows going against the constraints causes the referential integrity constraint violation.

Let's see an example considering the above two relations.

The Branch_Code of a tuple in the relation Branch cannot update from CSE to CS as the referencing attribute Branch_Code references value CSE in the referencing relation Student.

Handling the Violations

The referential integrity constraint violation caused by the update from a referenced relation gets handled in the following three ways:

• It is by updating the tuples from the referencing relation simultaneously where the referencing attribute has the value of the referenced attribute to be updated. This way of constraint violation handling is known as On Update Cascade.
• The second method is to abort or delete the deletion request of the referenced relation if the value to update is present in the referencing attribute of the referencing relation.
• The third method involves replacing the value with NULL or another value in the referencing relation if the referencing attribute uses the value updated in the referenced relation.