Quick Contact

    Relational Integrity Constraints

    The constraints are fundamentally the set of rules used for reducing the information that can be saved in the database. This is referred to as the relational integrity constraints.

    Maintaining the protection and integrity of a database is one of the essential factors that need to be taken in measuring the fulfilment of the system.

    Types of Relational Integrity Constraints

    There are five major types of relational integrity constraints are as follows

    Relational Integrity Constraints
    Key Constraint

    An attribute that can uniquely recognize a row in a relation is known as the key of the table. The value of the attribute for several rows in the relationship has to be specific.

    Example

    Consider the following Employee Table

    EMP-ID NAME AGE
    E-123 Himanshu 25
    E-124 Akshay 24
    E-123 Garvit 26
    E-125 Saksham 22

    Note:This is not allowed because all the rows need to be unique.

    Tuple Uniqueness Constraint

    Tuple Uniqueness constraint defines that all the tuples have to be particular specific in any relation.

    Example

    Consider the following Employee Table

    EMP-ID NAME AGE
    E-123 Himanshu 25
    E-124 Akshay 24
    E-125 Garvit 26
    E-126 Saksham 22

    This association satisfies the tuple uniqueness constraint because here, all the tuples are unique.

    Entity Integrity Constraint

    Entity integrity constraint represents that no characteristics of the primary key can have an invalid cost.

    Example

    A null value for an attribute defines that either the value is not known or is not applicable. Therefore, if an association has a null value for a primary attribute, then we cannot protect the unique feature of the primary key.

    Consider the following Employee Table

    EMP-ID NAME AGE
    E-123 Himanshu 25
    E-124 Akshay 24
    Garvit 26
    Saksham 22

    Note: This is not allowed because the primary key cannot have a null value.

    Referential Integrity Constraint

    The referential integrity constraint requires a relationship among relations.

    In the Referential integrity constraints, if a foreign key in table A refers to the primary key of table B, then each value of the foreign key in table A should be null or be feasible in table B.

    Example

    Consider the following Employee Table and Department Table

    Relational Integrity Constraints
    Domain Constraint

    A domain is a set of proper values related to attributes. Each attribute has a set of authorized values connect to it and is known as Domain of that attribute. Domain constraints test that the values used for the columns in the association must be valid and is from the domain connect to that attribute.

    It determines that the cost of each attribute P has to be an atomic cost from the area of P. The data type related to domains involves integer, character, string, date, and time, etc. An attribute value should be possible in the correlating domain.

    Example

    Consider the following Employee Table

    EMP-ID NAME AGE
    E-123 Himanshu 25
    E-124 Akshay 24
    Garvit 26
    Saksham A

    Note: This is not allowed because AGE is an integer attribute.

    Types of Domain Constraints
    1. Not Null Constraint

      The not null constraints secure that the clients continually sort the value for that column. A not-null constraint can be tested at the column level only.

    2. Check Constraint

      Check constraints secure that when the record is entered, the data in the column is finite to particular values.

    3. Default Constraint

      The default constraint give the default rate for the column to which it is linked when nothing is stated for that.

    Update Operations on Relations

    Updating the relation define changing the values present in the rows. These changes can be done either for an individual row or multiple rows. We can apply this update operation on an individual table only.

    Syntax
    	Update < table name>
    	Set < column defintion1, column definition2……..>
    	Where < condition is true>;
    
    There are three essential update operation on relations
    1. Insert Operation: It is used to insert a new tuple(s) in a relation.
    2. Delete Operation: It is used to remove tuple(s).
    3. Update or Modify Operation: It is used to modify the values of particular attributes in current tuples.

    Whenever an update operation is carried out, the integrity constraint should not be violated.

    Insert Operation

    The insert operation can violate several of the four types of constraints.

    1. Domain Constraint
    2. Key Constraint
    3. Entity Integrity Constraint
    4. Referential Integrity Constraint
    Example

    Consider the Student table with Roll as a key.

    STUDENT
    Roll Name Age
    1001 Shivang 17
    1002 Monika 18
    1003 Rohan 19
    1. Insert <1001, ‘Rajesh’, 17> into STUDENT is not allowed because this insertion violates the key constraints and so it is rejected.
    2. Insert < null, ‘Mahesh’, 18> into STUDENT relation is not allowed because this insertion violates the entity integrity constraints (null for primary key Roll).
    3. Consider the following Table
    EMPLOYEE
    EMP-ID EMP-NAME AGE DNO
    121 Sunny 22 10
    122 Mohit 24 11
    123 Rohit 26 12
    DEPT
    DNO DLOCATION
    10 Karnal
    11 Ambala
    12 Sonipat

    Insert <124, “Ajay”, 27, 15> into EMP relation is not allowed because this insertion violates the referential integrity constraints because no DEPT tuple exists with DNO=15.Insert <124, “Ajay”, 27, 15> into EMP relation is not allowed because this insertion violates the referential integrity constraints because no DEPT tuple exists with DNO=15.

    Hence, if an insert violates unnecessary constraints, the default option is to dismiss the insertion. In this case, it would be useful if the DBMS could explain to the user why the insertion was denied.

    Delete Operation

    Delete process can disrupt only referential integrity.

    Example

    Consider the following tables. In EMP relation, ENO is a primary key, and DNO is a foreign key.

    EMPLOYEE
    EMP-ID EMP-NAME AGE DNO
    121 Sunny 22 10
    122 Mohit 24 11
    123 Rohit 26 12
    DEPT
    DNO DLOCATION
    10 Karnal
    11 Ambala
    12 Sonipat

    Delete <10, “Karnal”> from DEPT relation is not allowed because the deletion of this tuple will violate the referential integrity.

    Three options are possible if a deletion process causes a violation.

      1. Reject the deletion
      2. Cascade deletion

    In this option, delete the tuples that reference the tuple that is being deleted.

    Example

    Delete <10, “Karnal”> from DEPT relation causes deletion of <121, “Sunny”,22,10> from EMP relation automatically.

    1. Modifying the referencing attribute valuesChange the referencing attribute values that cause the violation of either null or another valid value.
      Example

      Delete <10, “Karnal”> from DEPT relation causes change in the tuples as <121, “Sunny”,22,11> from EMP relation.

    Update Operation

    Update an attribute that is neither a primary key nor a foreign key generally create no problem, only confirm that the new value is of the upgrade domain and data type. Customize a primary key value is equal to remove one tuple and insert another.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.