Database Operations and Constraint Violations - The Insert operation

Database Operations and Constraint Violations – The Insert Operation

The basic Database operations performed on a relation are –

Insert Operation – The insert operation is used to insert a new tuple(s) on relations.
Delete Operation – The delete operation is used to delete the tuple(s).
Update or Modify Operation – The update operation is used to change/update the values of some attributes in existing tuples.

CLICK ON THE OPERATIONS TO KNOW MORE Whenever these above operations are performed, the integrity constraints should not be violated. Before describing operations, let us understand the following figure. Consider two existing relations named EMPLOYEE and DEPARTMENT.

Insert -Database Operations and Constraint Violations

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 Insert Operation :

Insertion in a Referenced Relation (DEPARTMENT) – If we insert a tuple in the referenced relation , then it causes no violation. For example, Insertion of <13, ‘Karnal’> in DEPARTMENT relation is allowed because insertion of this tuple causes no violation.
Insertion in a Referencing Relation (EMPLOYEE) – The insert operation in a referencing relation can violate any of four types of constraints –
1. Domain Constraints – Insertion of the value <5, ‘Pooja’, 10, 11> into EMPLOYEE table is not allowed, Because this insertion violates the domain constraints as the employee age cannot be less than 18 years.
2. Key Constraints – Insertion of the value <3, ‘Anuja’, 19, 11> into EMPLOYEE table is not allowed, Because this insertion violates the key constraints as an employee with ENO(Primary Key) 3 already exists.
3. Entity Constraints – Insertion of the value <NULL, ‘Kavya’, 21, 10> into EMPLOYEE table is not allowed, Because this insertion violates the Entity Integrity constraints or Integrity Rule 1 as the primary key(ENO) cannot contain a null value.
4. Referential Integrity Constraints – Insertion of the value <6, ‘Ajit’, 19, 16> into EMPLOYEE table is not allowed, Because this insertion violates the Referential Integrity constraints or Integrity Rule 2 as there is no row or tuple with DNO=15 exists in DEPARTMENT relation.