US Trends

what is integrity constraints in dbms

Integrity constraints in DBMS are rules defined on a database to ensure that the stored data always remains accurate, consistent, and valid during insert, update, and delete operations.

What Is Integrity Constraints in DBMS? (Quick Scoop)

Think of a database as a very strict school: you can only enter if you follow the rules. Integrity constraints are those rules. They prevent wrong, incomplete, or conflicting data from entering or living in your tables.
  • They ensure data is:
    • Correct (no invalid values).
* Consistent (no contradictions between tables).

* Reliable over time (operations don’t “break” the data).

A simple example:

A student table should never have two different students with the same roll number, and a “marks” column should not accept negative values.

That “no duplicate roll” and “no negative marks” are integrity constraints.

Why Integrity Constraints Matter

  • They block invalid data before it pollutes the database (e.g., wrong email format, impossible dates).
  • They protect relationships between tables (e.g., no order pointing to a non‑existent customer).
  • They enforce business rules automatically (e.g., every order must have a customer, salaries can’t be below minimum).
  • They reduce bugs in applications because the database itself rejects bad data.

In modern apps (e‑commerce, fintech, social platforms), this is an evergreen topic because bad data can break analytics, reports, and even legal compliance.

Types of Integrity Constraints in DBMS

Below are the core types you’ll almost always see in exams and interviews.

1\. Domain Constraints

Domain constraints specify what kind of values are allowed in a column.
  • Restrict:
    • Data type (INT, VARCHAR, DATE, etc.).
* Range (e.g., age between 0 and 120).

* Format or set of allowed values (e.g., status ∈ {‘Active’, ‘Inactive’}).

Example:

sql

CREATE TABLE Employee (
    EmpID INT,
    Age INT CHECK (Age >= 18 AND Age <= 60),
    Email VARCHAR(100)
);

Here, Age has a domain constraint that only allows values between 18 and 60.

2\. Entity Integrity Constraint

Entity integrity ensures that every row (entity) in a table can be uniquely identified.
  • Implemented using a PRIMARY KEY.
  • Rules:
    • Primary key values must be unique.
* Primary key cannot be NULL.

Example:

sql

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

Now:

  • No two students can share the same RollNo.
  • RollNo cannot be empty.

3\. Referential Integrity Constraint

Referential integrity keeps relationships between tables valid.
  • Implemented using FOREIGN KEY.
  • Rule: A foreign key value must either:
    • Match an existing primary key in the parent table, or
    • Be NULL (if allowed).

Example:

sql

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

This ensures:

  • You cannot assign an employee to a department that doesn’t exist.
  • You cannot delete a department if employees still refer to it, unless you define ON DELETE rules.

4\. Key Constraints

Key constraints govern columns that uniquely identify rows or help form uniqueness.

Common key-related constraints:

  • PRIMARY KEY
    • Uniqueness + Not Null.
  • UNIQUE
    • Value must be unique, but can allow a single NULL (DB-specific).
  • CANDIDATE KEY
    • Any attribute (or set) that could be a primary key.
  • SUPER KEY
    • Any superset of a key that still uniquely identifies a row.

Example:

sql

CREATE TABLE UserAccount (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE
);

Both Username and Email must be unique in the table.

5\. NOT NULL Constraint

NOT NULL says: “This field must always have a value.”

Example:

sql

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerName VARCHAR(100) NOT NULL
);

OrderDate and CustomerName cannot be left empty.

6\. DEFAULT Constraint

DEFAULT provides a value when the user doesn’t specify one.

Example:

sql

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(50),
    Price DECIMAL(10,2) DEFAULT 0.0
);

If Price is omitted during insert, it becomes 0.0 automatically.

7\. CHECK Constraint

CHECK enforces a logical condition on a column (or multiple columns).

Example:

sql

CREATE TABLE Payment (
    PaymentID INT PRIMARY KEY,
    Amount DECIMAL(10,2) CHECK (Amount > 0),
    Mode VARCHAR(20) CHECK (Mode IN ('UPI', 'Card', 'Cash'))
);
  • Amount must be positive.
  • Mode must be one of the allowed values.

Mini Story: When Constraints Save the Day

Imagine you run an online course platform.
  • One night, a buggy script tries to insert students with:
    • Negative fees.
    • Duplicate student IDs.
    • Enrollments into non‑existent courses.

If your tables have integrity constraints:

  • Domain + CHECK will block negative fees.
  • Primary key / UNIQUE will block duplicate student IDs.
  • Foreign key will block enrollments to courses that don’t exist.

The next morning, your data is still clean.
Without constraints, your reports, leaderboards, and invoices would be a mess.

Quick HTML Table: Types & Purpose

[1][9] [1][9] [5][3][9] [5][9] [3][9] [3][9] [9][3] [9] [5][9] [2][9] [2][5] [2] [4][9] [4][9]
Constraint Type What It Controls Example Rule
Domain Allowed values in a column.Age must be between 18 and 60.
Entity Integrity Uniqueness of each row.Every student must have a unique, non‑NULL RollNo.
Referential Integrity Validity of relationships across tables.Every order must reference an existing customer.
Key Constraints Unique identifiers (primary, unique, candidate).Email must be unique for each user.
NOT NULL Column cannot be empty.OrderDate cannot be NULL.
DEFAULT Automatic value if none provided.Price defaults to 0.0 if not given.
CHECK Logical conditions on data.Salary must be greater than 0.

Exam-Style Short Answer

If you need a compact line for exams:

Integrity constraints in DBMS are predefined rules applied on table columns and relationships to ensure the validity, accuracy, and consistency of data during insert, update, and delete operations.

SEO Bits & Context

  • Focus keyword used: “what is integrity constraints in dbms” (plus related “trending topic”, “forum discussion”, “latest news” as light meta terms).
  • This concept stays relevant as databases power modern web apps, analytics, and compliance-heavy domains like finance and healthcare.

Bottom Note: Information gathered from public forums or data available on the internet and portrayed here.