Integrity Constraints in DBMS – Domain, Key, Entity & Referential

Integrity Constraints – Domain, Key, Entity, Referential

In the Relational Data Model, Integrity Constraints are rules that ensure the correctness and consistency of data in a database. These constraints help prevent invalid, duplicate, or mismatched data. They are essential for maintaining reliable and meaningful information in any system — such as a university or banking database in Bangladesh.

🔹 What are Integrity Constraints?

An Integrity Constraint is a rule applied to the database schema to maintain accurate and consistent data. They make sure that data inserted, updated, or deleted remains valid and logically correct.

Types of Integrity Constraints:

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

🟩 1. Domain Constraint

The Domain Constraint ensures that each attribute (column) in a relation contains only valid values from its defined domain.

Example (Bangladeshi Context):

  • If the domain of Age is INTEGER between 18 and 60, no student under 18 or over 60 can be added.
  • The District attribute can only contain values like ‘Dhaka’, ‘Rajshahi’, ‘Khulna’, etc.
CREATE TABLE Student (
  Student_ID INT,
  Name VARCHAR(50),
  Age INT CHECK (Age BETWEEN 18 AND 60),
  District VARCHAR(30)
);
Student_IDNameAgeDistrict
101Rafi Ahmed21Dhaka
102Shanta Rahman23Khulna
103Tanvir Hasan19Rajshahi

Purpose: Ensures data validity and logical accuracy.

🟩 2. Key Constraint

The Key Constraint ensures that each record (tuple) in a table is uniquely identifiable. No two rows can have the same value for a primary key attribute.

Example:

In the Student table, Student_ID must be unique for each student.

CREATE TABLE Student (
  Student_ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT,
  District VARCHAR(30)
);
Student_IDNameAgeDistrict
201Ahsan Arif22Dhaka
202Rimi Akter20Chattogram
203Sumon Mia23Sylhet

Purpose: Prevents duplicate records in a table.

🟩 3. Entity Integrity Constraint

The Entity Integrity Constraint ensures that the Primary Key cannot be NULL. Every record must have a unique identifier that exists and is not empty.

Example:

Each student in a Bangladeshi university must have a valid Student_ID.

CREATE TABLE Student (
  Student_ID INT PRIMARY KEY NOT NULL,
  Name VARCHAR(50),
  Department VARCHAR(30)
);
Student_IDNameDepartment
301Mehedi HasanCSE
302Nusrat JahanEEE
NULL ❌Jewel RanaBBA

The last row is invalid because Student_ID cannot be NULL.

Purpose: Ensures each entity (record) is uniquely identifiable.

🟩 4. Referential Integrity Constraint

The Referential Integrity Constraint ensures that a foreign key value in one table must exist as a primary key value in another table.

Example (Bangladesh University Database):

CREATE TABLE Department (
  Dept_ID INT PRIMARY KEY,
  Dept_Name VARCHAR(50)
);

CREATE TABLE Student (
  Student_ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Dept_ID INT,
  FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
Department Table
Dept_IDDept_Name
10CSE
20EEE
30BBA

Student Table
Student_IDNameDept_ID
401Rashed Karim10 ✅
402Farzana Akter20 ✅
403Imran Hossain50 ❌

The third record is invalid because Dept_ID = 50 does not exist in the Department table.

Purpose: Ensures relationships between tables remain valid and consistent.

🔹 Summary Table

Constraint Type Description Bangladeshi Example
Domain Constraint Ensures valid data type or range. Age must be between 18–60 for Dhaka University students.
Key Constraint Ensures each record is unique. Every NID in the Citizen table is unique.
Entity Integrity Primary key cannot be NULL. Each student must have a valid Student_ID.
Referential Integrity Foreign key must match primary key. Each student’s Dept_ID must exist in Department table.

✅ Key Takeaways

  • Integrity constraints maintain accuracy, validity, and consistency of data.
  • They prevent duplicate, missing, or mismatched information in databases.
  • They are essential for university, banking, and government systems in Bangladesh.

database constraints in bangladesh, integrity constraints example, relational model in dbms, domain key entity referential, student table database example, university database bangladesh, dbms tutorial bengali

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)