Violation of constraints in relational database

Here, we will learn about the violations that can occur on a database as a result of any changes made in the relation.

There are mainly three operations that have the ability to change the state of relations, these modifications are given below:

1. Insert –
   To insert new tuples in a relation in the database.
2. Delete –
   To delete some of the existing relation on the database.
3. Update (Modify) –
   To make changes in the value of some existing tuples.

Whenever we apply the above modification to the relation in the database, the constraints on the relational database should not get violated.

Insert operation:
On inserting the tuples in the relation, it may cause violation of the constraints in the following way:

1. Domain constraint :
Domain constraint gets violated only when a given value to the attribute does not appear in the corresponding domain or in case it is not of the appropriate datatype.

Example:
Assume that the domain constraint says that all the values you insert in the relation should be greater than 10, and in case you insert a value less than 10 will cause you violation of the domain constraint, so gets rejected.

2. Entity Integrity constraint :
On inserting NULL values to any part of the primary key of a new tuple in the relation can cause violation of the Entity integrity constraint.

Example:

Insert (NULL, ‘Bikash, ‘M’, ‘Jaipur’, ‘123456’) into EMP 

The above insertion violates the entity integrity constraint since there is NULL for the
primary key EID, it is not allowed, so it gets rejected.

3. Key Constraints :
On inserting a value in the new tuple of a relation which is already existing in another tuple of the same relation, can cause violation of Key Constraints.

Example:

Insert (’1200’, ‘Arjun’, ‘9976657777’, ‘Mumbai’) into EMPLOYEE 

This insertion violates the key constraint if EID=1200 is already present in some tuple in the same relation, so it gets rejected.

Referential integrity :
On inserting a value in the foreign key of relation 1, for which there is no corresponding value in the Primary key which is referred to in relation 2, in such case Referential integrity is violated.

Example:
When we try to insert a value say 1200 in EID (foreign key) of table 1, for which there is no corresponding EID (primary key) of table 2, then it causes violation, so gets rejected.

Solution that is possible to correct such violation is if any insertion violates any of the constraints, then the default action is to reject such operation.

Deletion operation:
On deleting the tuples in the relation, it may cause only violation of Referential integrity constraints.

Referential Integrity Constraints :
It causes violation only if the tuple in relation 1 is deleted which is referenced by foreign key from other tuples of table 2 in the database, if such deletion takes place then the values in the tuple of the foreign key in table 2 will become empty, which will eventually violate Referential Integrity constraint.

Solutions that are possible to correct the violation to the referential integrity due to deletion are listed below:

1. Restrict –
   Here we reject the deletion.
2. Cascade –
   Here if a record in the parent table(referencing relation) is deleted, then the corresponding records in the child table(referenced relation) will automatically be deleted.
3. Set null or set default –
   Here we modify the referencing attribute values that cause violation and we either set NULL or change to another valid value