US Trends

what is data integrity in dbms

Data integrity in DBMS means keeping the data in a database accurate , consistent, complete, and trustworthy throughout its entire life cycle (insertion, update, delete, storage, and retrieval). It is achieved using rules and constraints so that no operation can insert, update, or delete data in a way that makes it invalid or contradictory.

Quick Scoop: Simple Definition

  • Data integrity in DBMS = data remains correct, consistent, and reliable in the database at all times.
  • No invalid, missing, or conflicting values should be allowed (for example, no order without a valid customer, no negative age, no duplicate primary key).
  • This is enforced using integrity constraints like primary key, foreign key, unique, not null, and check constraints.

Why Data Integrity Matters

  • Ensures accurate decisions: Applications and reports based on the database can be trusted only if the underlying data is accurate and consistent.
  • Prevents corruption and anomalies: Bad inserts/updates/deletes can break relationships between tables or create meaningless records.
  • Improves reliability and compliance: Many standards and regulations require data to be complete, consistent, and protected against unauthorized changes.

Example:
If a banking DB allows a transaction record with a non‑existent account number, balances and reports will become inconsistent and misleading.

Types of Data Integrity in DBMS

Most relational DBMS discussions break data integrity into four logical types.

  1. Entity integrity
    • Ensures every row (tuple) in a table is uniquely identifiable and has a non‑null primary key.
 * Implemented using: PRIMARY KEY (and often UNIQUE + NOT NULL).

 * Example: In a `Students` table, `student_id` must be unique and not null; you cannot have two students with the same ID or an “empty” ID.
  1. Referential integrity
    • Keeps relationships between tables valid by enforcing correct foreign key references.
 * A foreign key value must either be null (if allowed) or match a primary key value in the referenced table.

 * Example: Every `order.customer_id` must match an existing `customers.customer_id`; you cannot store an order for a deleted or non‑existent customer.
  1. Domain integrity
    • Ensures each column’s values are within a valid, predefined set (type, range, format, allowed values).
 * Implemented using: data types, CHECK constraints, NOT NULL, default values.

 * Examples:
   * A `price` column only accepts positive numbers, not text or negative values.

   * A `date_of_birth` column must contain a valid date, not “abc” or a future date.
  1. User‑defined integrity
    • Extra business rules that are not covered by the standard three types but are important for a specific organization.
 * Implemented via triggers, stored procedures, or custom constraints.

 * Example: A company rule: “Discount allowed only if purchase amount ≥ 100”; the DBMS enforces this with a custom rule so no record violates it.

How DBMS Enforces Data Integrity

DBMS systems use a mix of constraints and mechanisms to maintain integrity automatically.

  • Structural constraints in the schema:
    • PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.
  • Triggers and stored procedures:
    • Run additional logic before/after insert, update, delete to enforce business rules (user‑defined integrity).
  • Transaction control:
    • Using transactions (COMMIT/ROLLBACK) to ensure that a group of changes either all succeed or all fail, keeping the database consistent.
  • Validation and error checking:
    • Rejection of invalid data at insert/update time based on defined constraints.

Example:
Trying to insert an order with a non‑existent customer_id will cause the DBMS to throw an error and reject the insert because of the foreign key constraint, thus preserving referential integrity.

Short TL;DR

  • Data integrity in DBMS = keeping data accurate, consistent, complete, and reliable in the database at all times.
  • It is mainly enforced through entity, referential, domain, and user‑defined integrity using constraints, triggers, and transaction control.

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