What are Integrity Constraints in DBMS?

In this article post, you will learn all about Integrity Constraints in DBMS including Introduction, Categories, and Levels of Integrity Constraints, Types of Key Constraints, Primary Key, Unique Key, Foreign Key, Not Null, Check, Default, Indexes, Stored Procedures, Triggers, Importance of Integrity Constraints, How to Implement the Integrity Constraints, Best Practices for Application of Integrity Constraints, Difference between Primary Key, Foreign Key and Unique Key, and FAQs.

Introduction to Integrity Constraints in DBMS

Hey guys, Integrity constraints are a very essential element of the database management system. Integrity Constraints in DBMS maintain accurate, reliable, and consistent data in a database during any operation using SQL key constraints. SQL key constraints are used to specify rules for the data in a table.

Key constraints are used to limit the type of data that can insert into a table and making sure that the data is valid and accurate. If there is any mismatch between the constraint and the data operation, the action will be aborted by SQL. We will discuss the different types of integrity constraints as well as their importance in this topic.

Categories of Integrity Constraints in DBMS

Integrity constraints in DBMS define rules as per the business requirement to maintain data integrity while performing insertion, modifications, or deletion operations into the database. There are 4 categories of Data integrity as follows –

1. Entity Integrity- Primary Key, Unique, Not null, etc are examples.
2. Domain Integrity- Check and Default constraints are examples.
3. Referential Integrity- Foreign Key is an example.
4. User-defined Integrity- Stored Procedures, Triggers, Constraints, etc. on column and table labels are examples.

Levels of Integrity Constraints in DBMS

Integrity constraints in DBMS can be defined into two parts as follows –

1. Column Level- Column-level integrity constraints apply to a specific column.
2. Table Level- Table-level integrity constraints apply to the whole table.

Types of Key Constraints in SQL

Here are basically, Nine (9) Types of Key Constraints in SQL as follows –

1. Primary Key Constraints
2. Unique Key Constraints
3. Foreign Key or Reference Key Constraints
4. Not Null Key Constraints
5. Check Key Constraints
6. Default Key Constraints
7. User-Defined Indexes
8. Stored Procedures
9. User-Defined Triggers

1. Understanding Primary Key Constraints

Primary Key is a unique identifier and it enforces the uniqueness of the column on which they are defined. A clustered index is created automatically on the column where a Primary Key is defined. Primary Key does not allow Nulls or it is a combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table. Create primary key constraints as follows –

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE CONSTRAINT Primary_Constraint_Name PRIMARY KEY,
ColumnN DATATYPE
);

Example:

CREATE TABLE PK_Test
(
Vendor_Id VARCHAR(10) CONSTRAINT Pk_Vendor_Id PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL
);

Screenshot: Below screenshot for reference –

2. Understanding Unique Key Constraints

Unique Key constraints enforce the uniqueness of the column on which they are defined. A non-clustered index is created automatically on the columns where Unique Key is created. Unique Key allows only one NULL Value or it ensures that all values in a column are different. Create unique key constraints as follows –

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE CONSTRAINT Unique_Constraint_Name UNIQUE,
ColumnN DATATYPE
);

Example:

CREATE TABLE UK_Test
(
Vendor_Id VARCHAR(10) CONSTRAINT Uk_Vendor_Id UNIQUE,
Vendor_Name VARCHAR(30)
);

Screenshot: Below screenshot for reference –

3. Understanding Foreign Key or Reference Key Constraints

A foreign key or reference key constraint ensures that all values exist in the particular column of a table that is referenced to the primary key or unique key of another table. A foreign key links two tables together and it refers to the primary or unique key in another table. The table that holds the foreign key is called a child table and that holds the primary key or unique key is called the parent table. To apply a foreign key refer below –

Syntax:

CREATE TABLE Table_Name1
(
Column1 DATATYPE CONSTRAINT Primary_Key_Name PRIMARY KEY);


CREATE TABLE Table_Name2
(
Column1 DATATYPE  PRIMARY KEY CONSTRAINT Foreign_Key_Name FOREIGN KEY REFERENCES Table_Name1(Column1),
Column2 DATATYPE NOT NULL);

Example:

CREATE TABLE FK_Vendor_Master
(
Vendor_Id VARCHAR(10) CONSTRAINT pk_Vendor_Id_Master PRIMARY KEY);

CREATE TABLE FK_Vendor
(
Vendor_Id VARCHAR(10)  PRIMARY KEY CONSTRAINT fk_Vendor_Id FOREIGN KEY REFERENCES FK_Vendor_Master(Vendor_Id),
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

In the above query results, We have created a table name FK_Vendor and another table name FK_Vendor_Master where we have implemented referential integrities i.e. Foreign Key.

4. Understanding Not Null Key Constraints

A not null Key constraints are a constraint that ensures a column cannot have a NULL value. Not-null is a column-level integrity constraint that applies to a specific column. Create a not-null constraint as follows –

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE,
ColumnN DATATYPE NOT NULL);

Example:

CREATE TABLE NN_Vendor
(
Vendor_Id VARCHAR(10),
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

In the above query results, We have created a table name NN_Vendor where we have implemented not-null integrity.

5. Understanding Check Key Constraints

Check constraints ensure that all the given values for a specific column are satisfied with a specific check condition to perform the operation in the database. It throws an error and aborts the operation if the specific check condition has failed. Check constraints come under domain integrity. Create a check key constraint as follows –

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE,
Column2 DATATYPE,
ColumnN DATATYPE CONSTRAINT Check_Constraint_Name CHECK (ColumnN>=Value));

Example:

CREATE TABLE CK_Vendor (
Vendor_Id VARCHAR(10),
Vendor_Name VARCHAR(30),
Age INT CONSTRAINT ck_Vendor_Age CHECK (Age>=18)
);

Screenshot: Below screenshot for reference –

In the above query results, We have created a table name CK_Vendor where we have implemented check constraint integrity.

6. Understanding Default Key Constraints

Default constraints ensure the insertion of a specified default value against the specific column if no data is provided by the user.

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE,
Column2 DATATYPE CONSTRAINT Default_Constraint_Name DEFAULT 'Value',
ColumnN DATATYPE);

Example:

CREATE TABLE df_Vendor (
Vendor_Id VARCHAR(10),
Vendor_Name VARCHAR(30) CONSTRAINT df_Vendor_Name DEFAULT 'UNKNOWN',
Age INT
);

Screenshot: Below screenshot for reference –

In the above query results, We have created a table name df_Vendor where we have implemented default integrity constraint.

7. Understanding User-Defined Indexes

An index is used to create and retrieve data from a database very quickly. Its structure looks like an index of a book or dictionary which helps to find any content in a very fast and efficient way. An index works like a pointer for data in a table of a database. Whenever a Primary key or a Unique key is created on a column of a table, an index is automatically created. Indexes play a very important role in databases for fast retrieval of data. Create an index as follows –

Syntax:

CREATE INDEX Index_Name ON Table_Name (Column_Name);

OR

CREATE INDEX Index_Name ON Table_Name (Column1, Column2, ColumnN);

Example: We will create an index on the Employee_ID column in the Employee_New1 table for fast retrieval of data as follows –

CREATE INDEX idx_Employee_ID
ON Employee_New1 (Employee_ID);

Screenshot: Below screenshot for reference –

Now, We can see in the above screenshot that an index has been created on the Employee_ID column in the Employee_New1 table that will help us with the fast retrieval of data.

8. Understanding Stored Procedures

A stored procedure is a very powerful part of the Microsoft SQL server that helps greatly to work with data and database configuration. Stored procedures are pre-compiled T-SQL statement that is saved under the stored procedures node into the database. We can perform any operation in the database using stored procedures including data controls as well as constraints. It is highly recommended to become familiar with this which is a very useful element of SQL in DBMS.

Stored Procedure Overview

We will create a SP named ‘sp_InsrtIntoEmployee_New1_Tbl’ to insert a record into the existing table ‘Employee_New1’ on the basis of the user has filled the correct value into a specific input parameter named ‘@Mode’ in the SP.

Said SP will take all inputs data to insert into the table if the user puts ‘Insert’ into the @Mode parameter but if the user inputs anything instead of ‘Insert’ into the @Mode input parameter then data will not be inserted into the table and display the available data –

Syntax:

CREATE PROC Proc_Name(
@InputParameterVariable DATATYPE,
@InputParaColumn1 DATATYPE,
@InputParaColumn2 DATATYPE,
@InputParaColumnN DATATYPE)
AS
BEGIN
IF(@InputParameterVariable ='Value')
BEGIN
DML Statement 1
END
ELSE
BEGIN
DML Statement 2
END
END

Example: We have already a table named Employee_New1 with us, now we will create a stored procedure named ‘sp_InsrtIntoEmployee_New1_Tbl’ to insert a record into the existing table named ‘Employee_New1’ as follows –

CREATE PROC sp_InsrtIntoEmployee_New1_Tbl(
@Mode VARCHAR(10),
@Employee_ID BIGINT,
@Salutation VARCHAR(6),
@First_Name NVARCHAR(15),
@Middle_Name NVARCHAR(15),
@Last_Name NVARCHAR(15),
@Full_Name NVARCHAR(50),
@Department_ID VARCHAR(10),
@Gender VARCHAR(15),
@Date_Of_Birth Date,
@Mobile_No VARCHAR(10))
AS
BEGIN
IF(@Mode ='Insert')
BEGIN
INSERT INTO Employee_New1 (Employee_ID, Salutation, First_Name, Middle_Name, Last_Name, Full_Name, Department_ID, Gender, Date_Of_Birth, Mobile_No) VALUES 
(@Employee_ID,@Salutation,@First_Name,@Middle_Name,@Last_Name,@Full_Name,@Department_ID,@Gender,@Date_Of_Birth,@Mobile_No)
END
ELSE
BEGIN
SELECT * FROM Employee_New1 WHERE Employee_ID=@Employee_ID
END
END

Screenshot: Below screenshot for reference –

Now, We will input all data values into the stored procedure and execute by putting the wrong value as ‘Delete’ instead of ‘Insert’ into the @Mode input parameter. In this scenario, data should not be inserted into the table, as follows –

Example:

DECLARE	@return_value int
EXEC	@return_value = [dbo].[sp_InsrtIntoEmployee_New1_Tbl]
		@Mode = N'Delete',
		@Employee_ID = 112,
		@Salutation = N'Mr',
		@First_Name = N'Sourabh',
		@Middle_Name = N'Kumar',
		@Last_Name = N'Tale',
		@Full_Name = N'Sourabh Kumar Tale',
		@Department_ID = N'001',
		@Gender = N'Male',
		@Date_Of_Birth = '2002-12-24',
		@Mobile_No = N'9876543210'
SELECT	'Return Value' = @return_value

Screenshot: Below screenshot for reference –

We can see in the above screenshot that the stored procedure doesn’t insert data into the table as the table data is displaying blank. Now, We will input the same data values into the stored procedure and execute by putting the correct value as ‘Insert’ into the @Mode input parameter. In this scenario, data should be inserted into the table, as follows –

Example:

DECLARE	@return_value int
EXEC	@return_value = [dbo].[sp_InsrtIntoEmployee_New1_Tbl]
		@Mode = N'Insert',
		@Employee_ID = 112,
		@Salutation = N'Mr',
		@First_Name = N'Sourabh',
		@Middle_Name = N'Kumar',
		@Last_Name = N'Tale',
		@Full_Name = N'Sourabh Kumar Tale',
		@Department_ID = N'001',
		@Gender = N'Male',
		@Date_Of_Birth = '2002-12-24',
		@Mobile_No = N'9876543210'
SELECT	'Return Value' = @return_value

Screenshot: Below screenshot for reference –

We can see in the above screenshot that the input parameter values are correct and hence all data has been inserted into the table. Now we will run a select statement to see the inserted data as follows –

Example:

SELECT * FROM Employee_New1;

Screenshot: Below screenshot for reference –

Now, as per the above screenshot, we can see that a record is inserted into the Employee_New1 table.

9. Understanding User-Defined Trigger

A trigger is a special kind of stored procedure that is automatically fired or executed when some event (insert, delete and update) occurs. Triggers are used to specify rules for the data that can insert, delete and update into a table. Triggers are ensures the accuracy and reliability of the data present in the table. If there is any violation of the rule and the data action, the action will be aborted by SQL.

User-Defined Trigger Overview

In this topic, we will create a trigger named ‘Trg_Employee_New1_Insert_Log’ for a specific table named ‘Employee_New1’ to write a log into a different table named ‘Employee_New1_Log’ on the insert operation. The trigger will be fired when any user will insert any data into the Employee_New1 table and writes a log automatically into the Employee_New1_Log table i.e. Employee_ID, Operation type, and log date as follows –

Syntax:

CREATE TRIGGER Trigger_Name ON Source_Table_Name 
FOR INSERT 
AS
INSERT INTO Log_Table_Name(Column1, Column2, ColumnN) 
SELECT Column1, 'INSERT', GETDATE() FROM INSERTED;

Example: We have already a table named Employee_New1 with us, and we will create a new table for maintaining logs named Employee_New1_Log before creating a trigger as follows –

CREATE TABLE Employee_New1_Log (
	LogID INT IDENTITY(1,1) NOT NULL,
	Employee_ID BIGINT NOT NULL,
	DB_Operation NVARCHAR(15) NOT NULL,
	LogDate DATETIME NOT NULL	
);

Screenshot: Below screenshot for reference –

Now, We will create a trigger to go ahead with as follows –

Example:

CREATE TRIGGER Trg_Employee_New1_Insert_Log ON Employee_New1 
FOR INSERT 
AS
INSERT INTO Employee_New1_Log(Employee_ID, DB_Operation, LogDate) 
SELECT Employee_ID, 'INSERT', GETDATE() FROM INSERTED;

Screenshot: Below screenshot for reference –

Now, the trigger has been created on Employee_New1 table and we will run the below select statement for both tables to check the values in both tables as follows –

Example:

SELECT * FROM Employee_New1;
SELECT * FROM Employee_New1_Log;

Screenshot: Below screenshot for reference –

As we can see both the tables are blank or have no data in them. Now we will insert a record into the Employee_New1 table.

Example:

INSERT INTO Employee_New1 (Employee_ID, Salutation, First_Name, Middle_Name, Last_Name, Full_Name, Department_ID, Gender, Date_Of_Birth, Mobile_No) VALUES 
('002','Mr', 'Raghunath', 'Dev','Anand','Raghunath Dev Anand','001','Male','2002-12-24','9876543210');

Screenshot: Below screenshot for reference –

Now, we have inserted one record into the Employee_New1 table. let’s run a select statement for both tables and see that a log should be inserted into the Employee_New1_Log table automatically as follows –

Example:

SELECT * FROM Employee_New1;
SELECT * FROM Employee_New1_Log;

Screenshot: Below screenshot for reference –

As per the above screenshot, we can see that a record is inserted into the Employee_New1 table, and a log has been inserted into the Employee_New1_Log showing a particular employee_id, DML operation as inserted and date of operation, etc.

Importance of Integrity Constraints in DBMS

Integrity constraints in DBMS are very important because they maintain the accuracy, reliability, and consistency of the data in the database during any operation. When we enforce certain business rule and conditions on the data, integrity constraints ensure that the data is valid and accurate and also improves the overall quality of the data and its usefulness in the database.

How to Implement the Integrity Constraints in DBMS?

We can implement the integrity constraints in DBMS as follows –

1. Identify the columns of a table that need to apply integrity constraints
2. Identify and define the needed type of constraint for required columns
3. Create and apply the appropriate integrity constraints on required columns of a table in the database
4. Do the proper testing of applied integrity constraints for its working properly using DML operations

Best Practices for Application of Integrity Constraints

Best practices for the application of integrity constraints are given below –

1. Understand the business requirement, and rules, and identify the appropriate type of integrity constraints
2. Identify the columns of a table and use the appropriate integrity constraints for each column
3. Do the proper testing of applied integrity constraints for its working properly using select, insert, delete, and update statements
4. Monitor the behavior of data regularly to ensure that integrity constraints are working fine and still data is valid

Difference between Primary Key and Foreign Key

Here are a few differences between Primary Key and Foreign Key as follows –

1. A primary key ensures that a specific column data is unique but a foreign key defines a link between the same column data in two tables.
2. Primary key identifies uniquely data in the table, but a foreign key refers to a table with another table with a primary key.
3. In a table only one primary key is allowed but more than one foreign key is allowed in a table.
4. Primary key does not allow NULL values but a foreign key contains NULL values.
5. Primary key is a combination of UNIQUE and no-null constraints but a foreign key contains duplicate values.

Difference between Primary Key and Unique Key

Here are a few differences between Primary Key and Unique Key as follows –

1. A primary key uniquely identifies each row but a unique key uniquely identifies a row with a combination of columns which is not a primary key.
2. Primary key doesn’t accept NULL values but the unique key can accept NULL values.
3. Only one primary key is allowed in a table but more than one unique key can be defined in a table.
4. Primary key creates a clustered index but the unique key creates a non-clustered index automatically.
5. Primary key supports auto-incremental values but the unique key doesn’t support auto-incremental values.

FAQs

Conclusion

This tutorial post described the basic knowledge of Integrity Constraints in DBMS. Integrity constraints in DBMS are very important because they maintain the accuracy, reliability, and consistency of the data in the database during any operation. It will provide enough understanding for beginners and professionals. We hope you would have liked this tutorial very well.

Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What is Database Language?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL