Database Operations and Constraint Violations in DBMS – 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 about Violations
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.
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 –
- 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.
- 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.
- 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.
- 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.
- Domain Constraints –
|Types of Constraints in DBMS or Relational Constraints||Constraint Operations during
Incoming search terms:
- key in DBMS
- operational constraints in dbms
- constraint violations in dbms
- different types of update operations on a relation
- constraint violations
- insert operation in dbms
- constraints violations in delete operation in sql
- constraint violation
- deletion operation can causes violation in sql
- constraints violations