EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials DBMS Tutorial Integrity Constraints in DBMS

Integrity Constraints in DBMS

Priya Pedamkar
Article byPriya Pedamkar

Updated April 1, 2023

Integrity Constraints in DBMS

Introduction to Integrity Constraints in DBMS

Integrity constraints in Database Management Systems (DBMS) are a set of rules that are applied on the table columns or relationships to ensure that the overall validity, integrity, and consistency (i.e. the quality) of the data present in the database table is maintained. Each and every time a table insert, update, delete, or alter operation is performed, it is evaluated against the terms or rules mentioned in the integrity constraint. The data is inserted, updated, deleted, or altered only if the result of the constraint comes out to be True. Thus, integrity constraint prevents accidental damage to the database by an authorized user.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Types of Integrity Constraints in DBMS

in relational DBMS, we primarily have four types of integrity constraints, namely :

Types of Integrity Constraints in DBMS

  • Domain Integrity Constraint
  • Entity Integrity Constraint
  • Referential Integrity Constraint
  • Key Constraints

In this particular article, we will try to learn different types of integrity with the help of a few examples.

1. Domain Integrity Constraint

A domain integrity constraint is a set of rules that restricts the kind of attributes or values a column or relation can hold in the database table. For example, we can specify if a particular column can hold null values or not, if the values have to be unique or not, the data type or size of values that can be entered in the column, the default values for the column, etc.

For example, we want to create a “customer_details” table, with information such as customer id, customer name, the number of items purchased, date of purchase, etc. So, in order to ensure domain integrity, we can specify the customer_id has to be unique, the quantity of items purchased has to be an integer number only and the date of purchase has to be a date or timestamp, etc. This can be achieved in the following manner while creating the table.

CREATE TABLE customer_details
(
customer_id character varying(255) NOT NULL,
customer_name character varying(255) NOT NULL,
quantity integer NOT NULL,
date_purchased date
);

Or we can do it using the browser menu as shown in the pictures below.

customer_details

Go to the Browser section -> Select the required database from the Databases -> Select the required schema from the Schemas -> Right-click on the Tables -> Choose Create Table … -> Fill in the details on the dialog box -> In the columns tab mention the domain constraint that you want.

Now let’s try to make an insert query and check if the domain constraints have been successfully applied.

INSERT INTO public.customer_details(
customer_id, customer_name, quantity, date_purchased)
VALUES ('US1002','Kabir Khan','ABC', 2019-12-31);

Integrity Constraints in DBMS2

In the above image, we can see that for the ‘quantity’ attribute when we tried to insert a character value instead of an integer, the domain constraint enables the server to throw an error.

2. Entity Integrity Constraint

Entity Integrity Constraint is used to ensure the uniqueness of each record or row in the data table. There are primarily two types of integrity constraints that help us in ensuring the uniqueness of each row, namely, UNIQUE constraint and PRIMARY KEY constraint.

The unique key helps in uniquely identifying a record in the data table. It can be considered somewhat similar to the Primary key as both of them guarantee the uniqueness of a record. But unlike the primary key, a unique key can accept NULL values and it can be used on more than one column of the data table.

CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255) UNIQUE,
Age int,
PRIMARY KEY (Student_ID)
);

Integrity Constraints in DBMS3

Now let’s try to make an insert query and check if the entity constraints have been successfully applied.

INSERT INTO public.students(
student_id, student_name, class_name, age)
VALUES (32,'ABC','V',12),(32,'XYZ','V',11);

Integrity Constraints in DBMS4

We can see in the above image that when we tried to insert duplicate values, the entity constraints forced the server to throw errors.

3. Referential Integrity Constraint

Referential Integrity Constraint ensures that there always exists a valid relationship between two tables.This makes sure that if a foreign key exists in a table relationship then it should always reference a corresponding value in the second table or it should be null.

We can create relationships between two tables in the following manner. Here, we have created a “Department” table and then “Employees” where the “department” attribute references to Department_ID” in the former table.

CREATE TABLE Department(
Department_ID int NOT NULL,
Department_Name varchar(255) NOT NULL,
PRIMARY KEY(Department_ID)
);
CREATE TABLE Employees(
Employee_ID int NOT NULL,
Employee_Name varchar(255) NOT NULL,
Department int NOT NULL,
Age int,
FOREIGN KEY (Department) REFERENCES Department(Department_ID)
);

After a few insertion operations, the data in the “Department” table looks something like this.

Department table

Now let’s try to make an insert query and check if the entity constraints have been successfully applied.

INSERT INTO public.employees(
employee_id, employee_name, department, age)
VALUES (1002,'K K Davis',10,43);

entity constraints

In the above example, we tried to insert a department that does not exist in the “Department” table, hence it gives an error. 

4. Key Constraints

There are a number of key constraints in SQL that ensure that an entity or record is uniquely or differently identified in the database. There can be more than one key in the table but it can have only one primary key.

Some of the key constraints in SQL are :

  1. Primary Key Constraint
  2. Foreign Key Constraint
  3. Unique Key Constraint

Conclusion

Integrity constraints in DBMS are a set of rules that are required to ensure consistency and integrity of data in the database.

Recommended Articles

We hope that this EDUCBA information on “Integrity Constraints in DBMS” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. What is RDBMS?
  2. Specialization in DBMS
  3. ER Model in DBMS
  4. Normal Forms in DBMS
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more