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.

Summary
Constraints in DBMS | Types of Constraints in DBMS
Article Name
Constraints in DBMS | Types of Constraints in DBMS
Description
Relational Constraints in DBMS are the constraints imposed to ensure database correctness. Types of constraints in DBMS- Domain Integrity Constraint, Referential Integrity Constraint, Tuple Uniqueness Constraint, Key Constraint, Entity Integrity Constraint.
Author
Publisher Name
Gate Vidyalay
Publisher Logo
Liked this article? Share it with your friends and classmates now-