Integrity Constraints in DBMS: Detailed Guide with Practical Examples

Updated on August 8, 2024

Article Outline

The Importance of Integrity Constraints in Database Management Types of Integrity Constraints in DBMS Practical Examples of Integrity Constraints Benefits of Implementing Integrity Constraints in Databases Challenges and Considerations When Applying Integrity Constraints Conclusion FAQs

Why do data in our databases sometimes end up wrong? How do we ensure our information stays correct and reliable?

These are questions many of us face when managing databases.

Integrity constraints in DBMS help us keep data accurate and consistent.

They are rules that databases follow to maintain the quality of the information. Whenever we add, update, or delete data, these constraints make sure everything stays in order. This means our data remains reliable and useful.

Let’s dive deep into the concept of integrity constraints in DBMS and understand its different types, uses, and benefits with appropriate examples.

The Importance of Integrity Constraints in Database Management

Why are integrity constraints so important?

First, they ensure our data stays accurate.

Imagine entering a date in the wrong format. With integrity constraints, such errors get flagged immediately. Without these rules, we might end up with incorrect information. This saves us from potential headaches later.

Second, they help keep our data consistent.

For instance, if one table references another, constraints make sure that the relationship stays valid. No more orphaned records or broken links.

Third, they enhance data reliability.

By preventing invalid data entries, we trust that our database holds only valid information. This is crucial for decision-making and reporting.

Lastly, integrity constraints aid in data validation.

They enforce specific rules for data entry, ensuring every piece of information meets our standards. This means our database remains a dependable resource.

Also Read: DBMS Tutorial for Beginners

Get curriculum highlights, career paths, industry insights and accelerate your technology journey.

Download brochure

Types of Integrity Constraints in DBMS

There are six types of integrity constraints:

types

Domain Constraints

Domain constraints limit the values that can be entered into a column. They define a valid set of values for an attribute.

For example, a “price” column should only accept positive numbers. When we try to insert a negative price, the database will reject it.

Here’s a simple example in SQL:

CREATE TABLE Products (

Product_ID INT,

Product_Name VARCHAR(100),

Price DECIMAL CHECK (Price > 0)

);

Not-Null Constraints

Not-null constraints ensure that a column cannot have a null value. This is vital for fields that must always contain data, like email addresses.

Look at this example:

CREATE TABLE Students (

Student_ID INT,

Student_Name VARCHAR(100),

Email VARCHAR(100) NOT NULL

);

If we try to add a student without an email, the database won’t allow it.

Entity Integrity Constraints

Entity integrity constraints make sure that primary keys are unique and not null. Primary keys identify each row in a table uniquely.

For an example:

CREATE TABLE Employees (

Employee_ID INT PRIMARY KEY,

Employee_Name VARCHAR(100),

Department VARCHAR(50)

);

Trying to insert a row with a duplicate or null Employee_ID will fail.

Key Constraints

Key constraints ensure that every key in an entity set is unique. An entity can have multiple keys, but one will be the primary key.

Here is an example:

CREATE TABLE Library (

ISBN VARCHAR(13) UNIQUE,

Book_Title VARCHAR(100),

Author VARCHAR(100)

);

No two books can have the same ISBN in this table.

Primary Key Constraints

Primary key constraints enforce uniqueness and non-null properties on the primary key attributes.
They ensure each record in the table is uniquely identifiable.

Let’s make it clear with an example:

CREATE TABLE Customers (

Customer_ID INT PRIMARY KEY,

Customer_Name VARCHAR(100),

Contact_Number VARCHAR(15)

);

Every Customer_ID must be unique and not null.

Referential Integrity Constraints

Referential integrity constraints maintain the relationship between tables. They ensure foreign keys in one table correspond to primary keys in another.

Let’s try to understand it with an example:

CREATE TABLE Orders (

Order_ID INT,

Customer_ID INT,

Order_Date DATE,

FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)

);

An order with a Customer_ID that does not exist in the Customers table will be rejected.

Practical Examples of Integrity Constraints

Example of Domain Constraints

Domain constraints limit the type of data that can be entered into a column.

Consider a product catalogue where the price must always be a positive number. Here’s how we can enforce this in SQL:

CREATE TABLE Products (

Product_ID INT,

Product_Name VARCHAR(100),

Price DECIMAL(10, 2) CHECK (Price > 0)

); 

-- Inserting valid data

INSERT INTO Products (Product_ID, Product_Name, Price)

VALUES (1, 'Laptop', 1500.00),

(2, 'Mouse', 25.75); 

-- Trying to insert invalid data

INSERT INTO Products (Product_ID, Product_Name, Price)

VALUES (3, 'Keyboard', -50.00);

Expected Output:

Product_ID	Product_Name	Price
1	Laptop	1500
2	Mouse	25.75

The last insert statement will fail because the price is negative, and it will cause an “invalid entry due to domain constraint.”

Example of Not-Null Constraints

Not-null constraints ensure that certain fields must always contain data.

For example, a student database where every student must have an email address.

CREATE TABLE Students (

Student_ID INT,

Student_Name VARCHAR(100),

Email VARCHAR(100) NOT NULL

); 

-- Inserting valid data

INSERT INTO Students (Student_ID, Student_Name, Email)

VALUES (101, 'Sonali', 'sonali@example.com'),

(102, 'Aditi', 'aditi@example.com'); 

-- Trying to insert invalid data

INSERT INTO Students (Student_ID, Student_Name, Email)

VALUES (103, 'Neha', NULL);

Expected Output:

Student_ID	Student_Name	Email
101	Sonali	sonali@example.com
102	Aditi	aditi@example.com

The last insert statement will fail because the email is missing, and it will cause an “invalid entry due to not-null constraint.”

Example of Entity Integrity Constraints

Entity integrity constraints make sure primary keys are unique and not null. Consider an employee table where each employee has a unique ID.

Here’s the SQL code:

CREATE TABLE Employees (

Employee_ID INT PRIMARY KEY,

Employee_Name VARCHAR(100),

Department VARCHAR(50)

); 

-- Inserting valid data

INSERT INTO Employees (Employee_ID, Employee_Name, Department)

VALUES (1, 'Murali', 'HR'),

(2, 'Suman', 'IT'); 

-- Trying to insert invalid data

INSERT INTO Employees (Employee_ID, Employee_Name, Department)

VALUES (NULL, 'Vinod', 'Sales');

Expected Output:

Employee_ID	Employee_Name	Department
1	Murali	HR
2	Suman	IT

The last insert statement will fail because the Employee_ID is null, and it will cause an “invalid entry due to entity integrity constraint.”

Example of Key Constraints

Key constraints ensure every key in an entity set is unique. Think about a library database where each book must have a unique ISBN number.

Here’s how it looks in SQL:

CREATE TABLE Library (

ISBN VARCHAR(13) UNIQUE,

Book_Title VARCHAR(100),

Author VARCHAR(100)

); 

-- Inserting valid data

INSERT INTO Library (ISBN, Book_Title, Author)

VALUES ('978-3-16-148410-0', 'The Great Gatsby', 'F. Scott Fitzgerald'),

('978-1-56619-909-4', '1984', 'George Orwell'); 

-- Trying to insert invalid data

INSERT INTO Library (ISBN, Book_Title, Author)

VALUES ('978-3-16-148410-0', 'To Kill a Mockingbird', 'Harper Lee');

Expected Output:

ISBN	Book_Title	Author
978-3-16-148410-0	The Great Gatsby	F. Scott Fitzgerald
978-1-56619-909-4	1984	George Orwell

The last insert statement will fail because the ISBN is duplicated, and it will cause an “invalid entry due to key constraint.”

Example of Primary Key Constraints

Primary key constraints enforce uniqueness and non-null properties on the primary key. Consider a customer database where each customer ID must be unique and not null.

CREATE TABLE Customers (

Customer_ID INT PRIMARY KEY,

Customer_Name VARCHAR(100),

Contact_Number VARCHAR(15)

); 

-- Inserting valid data

INSERT INTO Customers (Customer_ID, Customer_Name, Contact_Number)

VALUES (1, 'Divyansh', '555-1234'),

(2, 'Krish', '555-5678'); 

-- Trying to insert invalid data

INSERT INTO Customers (Customer_ID, Customer_Name, Contact_Number)

VALUES (NULL, 'Anupriya', '555-9876');

Expected Output:

Customer_ID	Customer_Name	Contact_Number
1	Divyansh	555-1234
2	Krish	555-5678

The last insert statement will fail because the Customer_ID is null, and it will cause an “invalid entry due to primary key constraint.”

Example of Referential Integrity Constraints

Referential integrity constraints maintain relationships between tables. Think about a sales database where each order must refer to a valid customer ID.

Here’s how we enforce this:

CREATE TABLE Customers (

Customer_ID INT PRIMARY KEY,

Customer_Name VARCHAR(100),

Contact_Number VARCHAR(15)

); 

CREATE TABLE Orders (

Order_ID INT,

Customer_ID INT,

Order_Date DATE,

FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)

); 

-- Inserting valid customer data

INSERT INTO Customers (Customer_ID, Customer_Name, Contact_Number)

VALUES (1, 'Manish', '555-1234'),

(2, 'Pankaj', '555-5678'); 

-- Inserting valid order data

INSERT INTO Orders (Order_ID, Customer_ID, Order_Date)

VALUES (1, 1, '2024-08-01'),

(2, 2, '2024-08-02'); 

-- Trying to insert invalid order data

INSERT INTO Orders (Order_ID, Customer_ID, Order_Date)

VALUES (3, 999, '2024-08-03');

Expected Output:

Customers Table:

Customer_ID	Customer_Name	Contact_Number
1	Manish	555-1234
2	Pankaj	555-5678

Orders Table:

Order_ID	Customer_ID	Order_Date
1	1	2024-08-01
2	2	2024-08-02

The last insert statement will fail because Customer_ID 999 does not exist in the Customers table, and it will cause an “invalid entry due to referential integrity constraint.”

Benefits of Implementing Integrity Constraints in Databases

Integrity constraints are more than just rules. They bring many benefits to our databases:

Enhanced Data Accuracy: Prevents incorrect data entry.
Improved Data Consistency: Maintains relationships between tables.
Increased Data Reliability: Ensures only valid data is stored.
Data Validation: Enforces specific rules for data entry.
Enhanced Security: Limits unauthorised data changes.
Better Database Performance: Optimizes query execution.

Challenges and Considerations When Applying Integrity Constraints

Integrity constraints in DBMS are essential for maintaining data quality. While integrity constraints are vital, they come with challenges:

Complexity in Database Design: Adding multiple constraints can make design more complex.
Performance Overhead: Integrity checks can slow down processing.
Flexibility Issues: Constraints may limit how we can modify data.

To overcome these challenges:

Plan Carefully: Think through the constraints before applying them.
Balance Needs: Ensure constraints provide benefits without excessive performance hits.
Regular Review: Periodically review and adjust constraints as needed.

Conclusion

Are you tired of dealing with messy, inaccurate data?

Integrity constraints in DBMS can save us from this headache. They ensure our data remains accurate, consistent, and reliable.

By implementing these constraints, we can avoid many common data problems.

We’ve seen how domain constraints prevent invalid data types. Not-null constraints make sure essential information isn’t missing. Entity integrity and key constraints keep our primary keys unique and present. Referential integrity constraints maintain relationships between tables.

These rules might seem strict, but they are crucial for data quality. Without them, our databases could quickly become chaotic and unreliable.

FAQs

Explain what integrity constraints are in the context of DBMS?

Integrity constraints are rules that check the validity and consistency of the data in the database. They are essential as they help preserve the quality and credibility of the data being collected.

What are the different types of integrity constraints in DBMS?

There are six types of integrity constraints in DBMS:

Domain constraints
Not-null constraints
Entity integrity constraints
Key constraints
Primary key constraints
Referential integrity constraints

How do referential integrity constraints work in a database?

Referential integrity constraints check that a foreign key in one table must match the primary key in another table. This maintains the relationship between tables and prevents orphaned records

Can integrity constraints improve database performance?

Yes, properly defined integrity constraints can improve performance by optimising query execution and ensuring efficient data retrieval and manipulation.

What challenges might arise when implementing integrity constraints?

Challenges include increased complexity in database design, potential performance overhead due to integrity checks, and reduced flexibility in data operations.

Updated on August 8, 2024

Link

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

3 ASSURED INTERVIEWS

Accelerator Program in

Business Analytics and Data Science

With

Part-time · 10 months

Apply by : 15 December, 2024

Financial Analysis, Valuation, & Risk Management

3 ASSURED INTERVIEWS

Certificate Program in

Financial Analysis, Valuation, & Risk Management

With

Part-time · 7 months

Apply by : 15 December, 2024

INTERNSHIP ASSURANCE

Certificate Program in

DevOps & Cloud Engineering

With

Part-time · 7.5 months

Apply by : 15 December, 2024

Upskill with expert articles

View all

Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Certification Program in Data Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance