Tag: types of integrity constraints in dbms

Constraints in DBMS | Types of Constraints in DBMS

Constraints in DBMS-

 

  • Relational constraints are the restrictions imposed on the database contents and operations.
  • They ensure the correctness of data in the database.

 

Types of Constraints in DBMS-

 

In DBMS, there are following 5 different types of relational constraints-

 

 

  1. Domain constraint
  2. Tuple Uniqueness constraint
  3. Key constraint
  4. Entity Integrity constraint
  5. Referential Integrity constraint

 

1. Domain Constraint-

 

  • Domain constraint defines the domain or set of values for an attribute.
  • It specifies that the value taken by the attribute must be the atomic value from its domain.

 

Example-

 

Consider the following Student table-

 

STU_IDNameAge
S001Akshay20
S002Abhishek21
S003Shashank20
S004RahulA

 

Here, value ‘A’ is not allowed since only integer values can be taken by the age attribute.

 

2. Tuple Uniqueness Constraint-

 

Tuple Uniqueness constraint specifies that all the tuples must be necessarily unique in any relation.

 

Example-01:

 

Consider the following Student table-

 

STU_IDNameAge
S001Akshay20
S002Abhishek21
S003Shashank20
S004Rahul20

 

This relation satisfies the tuple uniqueness constraint since here all the tuples are unique.

 

Example-02:

 

Consider the following Student table-

 

STU_IDNameAge
S001Akshay20
S001Akshay20
S003Shashank20
S004Rahul20

 

This relation does not satisfy the tuple uniqueness constraint since here all the tuples are not unique.

 

3. Key Constraint-

 

Key constraint specifies that in any relation-

  • All the values of primary key must be unique.
  • The value of primary key must not be null.

 

Example-

 

Consider the following Student table-

 

STU_IDNameAge
S001Akshay20
S001Abhishek21
S003Shashank20
S004Rahul20

 

This relation does not satisfy the key constraint as here all the values of primary key are not unique.

 

4. Entity Integrity Constraint-

 

  • Entity integrity constraint specifies that no attribute of primary key must contain a null value in any relation.
  • This is because the presence of null value in the primary key violates the uniqueness property.

 

Example-

 

Consider the following Student table-

 

STU_IDNameAge
S001Akshay20
S002Abhishek21
S003Shashank20
Rahul20

 

This relation does not satisfy the entity integrity constraint as here the primary key contains a NULL value.

 

5. Referential Integrity Constraint-

 

  • This constraint is enforced when a foreign key references the primary key of a relation.
  • It specifies that all the values taken by the foreign key must either be available in the relation of the primary key or be null.

 

Read more- Foreign Key in DBMS

 

Important Results-

 

The following two important results emerges out due to referential integrity constraint-

  • We can not insert a record into a referencing relation if the corresponding record does not exist in the referenced relation.
  • We can not delete or update a record of the referenced relation if the corresponding record exists in the referencing relation.

 

Example-

 

Consider the following two relations- ‘Student’ and ‘Department’.

Here, relation ‘Student’ references the relation ‘Department’.

 

 

Student

STU_IDNameDept_no
S001AkshayD10
S002AbhishekD10
S003ShashankD11
 S004RahulD14 

 

Department

Dept_noDept_name
D10ASET
D11ALS
D12ASFL
D13ASHS

 

Here,

  • The relation ‘Student’ does not satisfy the referential integrity constraint.
  • This is because in relation ‘Department’, no value of primary key specifies department no. 14.
  • Thus, referential integrity constraint is violated.

 

Handling Violation of Referential Integrity Constraint-

 

To ensure the correctness of the database, it is important to handle the violation of referential integrity constraint properly.

 

Next Article- Handling Violation of Referential Integrity Constraint

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.