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.

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 :

  1. 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 Referenced Relation (DEPARTMENT) - Database Operations and Constraint Violations
  2. 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.
      Domain Constraints - Database Operations with Constraint Violations
    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.
      Key Constraints - Database Operations and Constraint Violations
    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.
      Entity constraints - Database Operations and Constraint Violations
    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.
      Referential Integrity Constraints - Database Operations and Constraint Violations

Click on the Questions on Constraint Violations or Foreign Key


Previous Home Next
Types of Constraints in DBMS or Relational Constraints Constraint Operations during
DELETE Operation

     

Incoming search terms:

  • key in DBMS
  • constraint violations in dbms
  • operational constraints in dbms
  • super keys in dbms
  • constraints that are violated during delete and update operation
  • voilation of domine constrine while inseting a tuple
  • examples of update delete and insert in relationship model
  • explain update operation in dbms
  • different types of update operations on a relation
  • different constraints that are violated during delete operations

Leave a Reply