Integrity Constraints

Integrity Constraints are rules applied to database tables to ensure that the data stored is accurate, valid, and consistent.

Definition

Integrity Constraints वे नियम (Rules) हैं जो Database में गलत (Invalid) या Duplicate Data को आने से रोकते हैं और Data की Accuracy, Consistency और Reliability बनाए रखते हैं।

Simple Definition

Integrity Constraints = Database के Traffic Rules

जैसे सड़क पर Traffic Rules दुर्घटनाओं को रोकते हैं, वैसे ही Database में Integrity Constraints गलत डेटा को रोकते हैं।

---

Why Do We Need Integrity Constraints?

Imagine a Student table.

StudentID	Name	Age
101	Rahul	20
101	Aman	21
NULL	Neha	19
105	Priya	-5

Problems:

• Duplicate StudentID ❌
• NULL Primary Key ❌
• Negative Age ❌

These errors reduce data quality.

Integrity Constraints prevent such problems.

---

Objectives of Integrity Constraints

• Prevent invalid data.

• Maintain consistency.
• Avoid duplicate records.
• Protect relationships between tables.
• Improve data quality.
• Increase database reliability.

---

Types of Integrity Constraints

There are six major constraints.

              Integrity Constraints
                     │
 ┌──────────┬──────────┬──────────┬──────────┬──────────┬──────────┐
 │          │          │          │          │          │
Domain   Entity   Referential   Key      Check    Default

We will study each one in detail.

---

 Domain Constraint

Definition

A Domain Constraint restricts the values that can be stored in a column.

Every attribute has a predefined domain.

Example

Age

Allowed values:

18 – 60

Valid

25 30 45

Invalid

-5
120
ABC

---

Real-Life Example

Gender

Allowed values:

Male

Female

Other

Not allowed:

Apple

123

Teacher

---

SQL Example

CREATE TABLE Student
(
    Age INT CHECK(Age>=18 AND Age<=60)
);

---

Entity Integrity Constraint

Definition

The Primary Key of a table:

• Cannot be NULL.
• Must be unique.

---

Example

Student Table

StudentID	Name
101	Rahul
102	Aman

Valid ✔

---

Invalid

StudentID	Name
NULL	Rahul

❌ Primary Key cannot be NULL.

---

Duplicate

StudentID	Name
101	Rahul
101	Aman

❌ Duplicate Primary Key is not allowed.

---

SQL Example

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50)
);

---

Referential Integrity Constraint

Definition

A Foreign Key must always refer to an existing Primary Key value in the parent table.

---

Parent Table

Student

StudentID	Name
101	Rahul
102	Aman

---

Child Table

Enrollment

StudentID	Course
101	DBMS

Valid ✔

---

Invalid

StudentID	Course
999	DBMS

Student 999 does not exist.

❌ Not allowed.

---

Diagram

Student
------------------
StudentID (PK)

101

102

        │
        ▼

Enrollment

StudentID (FK)

101 ✔

999 ❌

---

SQL Example

CREATE TABLE Enrollment
(
StudentID INT,
Course VARCHAR(50),

FOREIGN KEY(StudentID)

REFERENCES Student(StudentID)
);

---

Why Referential Integrity?

Without it:

Student deleted

↓

Enrollment still exists

↓

Invalid database

Referential Integrity prevents this.

---

Parent and Child Tables

Parent Table

↓

Primary Key

↓

Referenced by

↓

Child Table

↓

Foreign Key

---

Key Constraint

Definition

Every table should have a key that uniquely identifies records.

Example

EmployeeID	Name
101	Rahul
102	Aman

EmployeeID is unique.

Duplicate EmployeeID is not allowed.

---

NOT NULL Constraint

Definition

A NOT NULL column must always contain a value.

---

Example

CREATE TABLE Student
(
Name VARCHAR(50) NOT NULL
);

Valid

Name
Rahul

Invalid

Name
NULL

---

UNIQUE Constraint

Definition

The UNIQUE constraint ensures that all values in a column are different.

---

Example

Email
rahul@gmail.com
aman@gmail.com

Valid ✔

Duplicate

Email
rahul@gmail.com
rahul@gmail.com

❌ Invalid

---

SQL Example

CREATE TABLE Student
(
Email VARCHAR(50) UNIQUE
);

---

Primary Key vs UNIQUE

Primary Key	UNIQUE
Only one per table	Multiple UNIQUE constraints allowed
Cannot be NULL	NULL handling depends on the DBMS
Main identifier	Prevents duplicate values

---

CHECK Constraint

Definition

CHECK verifies that a value satisfies a condition.

---

Example

Salary

Must be greater than 10,000.

CHECK(Salary>10000)

---

Age

CHECK(Age>=18)

---

Valid

Age = 25 ✔

Invalid

Age = 15 ❌

---

SQL Example

CREATE TABLE Employee
(
Salary INT CHECK(Salary>=10000)
);

---

DEFAULT Constraint

Definition

If no value is provided, the DEFAULT value is automatically inserted.

---

Example

Country

Default:

India

---

SQL

CREATE TABLE Student
(
Country VARCHAR(20)

DEFAULT 'India'
);

Insert

INSERT INTO Student(Name)

VALUES('Rahul');

Result

Name	Country
Rahul	India

---

Complete Student Table

CREATE TABLE Student
(
StudentID INT PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

Email VARCHAR(50) UNIQUE,

Age INT CHECK(Age>=18),

Country VARCHAR(20)

DEFAULT 'India'
);

---

Visual Summary

Student Table

StudentID

↓

Primary Key

↓

Unique

↓

NOT NULL

-------------------------

Email

↓

UNIQUE

-------------------------

Age

↓

CHECK

-------------------------

Country

↓

DEFAULT

-------------------------

DepartmentID

↓

FOREIGN KEY

---

Real-Life Example

Bank Database

Customer Table

CustomerID	Name
101	Rahul

Account Table

AccountNo	CustomerID
5001	101

Rules:

• CustomerID unique ✔
• CustomerID cannot be NULL ✔
• Account linked with Customer ✔
• Balance ≥ 0 ✔ (CHECK)

---

Advantages of Integrity Constraints

• Accurate data

• No duplicate records
• Strong relationships
• Better security
• Easy maintenance
• High reliability
• Better database performance

---

Disadvantages

• Slight overhead during insert/update operations
• Complex rules may slow large transactions
• Require proper database design