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.
Types of Integrity Constraints in DBMS
in relational DBMS, we primarily have four types of integrity constraints, namely :
- 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.
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);
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.
4.7 (3,220 ratings)
View Course
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)
);
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);
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.
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);
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 :
- Primary Key Constraint
- Foreign Key Constraint
- 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
This is a guide to Integrity Constraints in DBMS. Here we discuss an introduction to Integrity Constraints in DBMS and top 4 types with detail explanation. You can also go through our other related articles to learn more –
