DBMS Integrity Constraints

Maintaining accurate, consistent, and reliable data is one of the most important responsibilities of a Database Management System (DBMS). A database may contain thousands or even millions of records, so incorrect or inconsistent data can lead to unreliable results and broken relationships between tables.

Integrity constraints are rules applied to database tables to ensure that only valid and meaningful data is stored. These constraints control data during operations such as INSERT, UPDATE, and DELETE and prevent actions that could compromise database integrity.

What are Integrity Constraints?

Integrity constraints are predefined rules that are applied to columns, rows, or relationships between tables in a database. They specify what type of data can be stored and how different records are allowed to relate to each other.

If an operation violates an integrity constraint, the DBMS rejects the operation and prevents invalid data from being stored.

Integrity constraints can generally be defined in two ways:

Column Constraints: Defined directly with a column while creating a table.
Table Constraints: Defined separately at the end of the CREATE TABLE statement and can involve one or more columns.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Types of Integrity Constraints in DBMS

The major types of integrity constraints in DBMS are:

Domain Constraints
Entity Integrity Constraints
Referential Integrity Constraints
Key Constraints

1. Domain Constraints

Domain constraints define the valid values that can be stored in a particular attribute or column. They specify restrictions such as the data type, range, format, or allowed values for an attribute.

For example, an employee’s age should contain a valid numeric value. Similarly, a department ID that is defined as an integer should not contain a character value.

Example

emp_id   Name    Birth_year   Dept_id
10001    Raja    2000         001
10002    Tiya    2001         002
10003    Puja    1999         001
10004    Rohit   2002         B    Invalid

In the last record, Dept_id contains B. If the column is defined as an integer, this value violates the domain constraint.

CHECK Constraint

The CHECK constraint is commonly used to enforce domain-level conditions. It ensures that a value satisfies a specified condition before it is stored.

Column-Level Syntax:

column_name datatype
CONSTRAINT constraint_name CHECK (condition);

Table-Level Syntax:

CONSTRAINT constraint_name CHECK (condition);

Example

CREATE TABLE Employee (
    emp_id INT,
    age INT CHECK (age >= 18)
);

In this example, an employee age below 18 will not satisfy the defined condition.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values.

Syntax:

column_name datatype
CONSTRAINT constraint_name NOT NULL;

It is useful for attributes that must always contain a value, such as an employee name or registration number.

2. Entity Integrity Constraints

Entity integrity ensures that every row in a table can be uniquely identified. It is primarily enforced using a Primary Key.

A primary key must contain:

Unique values
Non-NULL values

Example

Roll_no   Name    Birth_year
41        Akash   2009
23        Mina    2008
14        Rony    2010
NULL      Tina    2009    Invalid

The last record violates entity integrity because Roll_no is the primary key and a primary key cannot contain a NULL value.

3. Referential Integrity Constraints

Referential integrity maintains consistency between related tables. It is enforced using a Foreign Key.

A foreign key value must either:

Match an existing primary key or unique key value in the referenced table, or
Be NULL when the foreign key column permits NULL values.

Example

Students Table:

Roll_no   Name     Branch_ID
11        Ronti    3
12        Priya    2
13        Puja     4

Branch Table:

Branch_ID   Branch_Name
1           CSE
2           EE
3           ME

The Branch_ID value 4 in the Students table is invalid because there is no corresponding Branch_ID = 4 in the Branch table.

Therefore, the record violates referential integrity.

Foreign Key Example

CREATE TABLE Students (
    Roll_no INT PRIMARY KEY,
    Name VARCHAR(50),
    Branch_ID INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

This relationship prevents a student from being assigned to a branch that does not exist in the referenced table.

4. Key Constraints

Key constraints help uniquely identify records and prevent unwanted duplicate values in a table.

Two commonly used key constraints are:

Primary Key Constraint
Unique Key Constraint

Primary Key Constraint

A Primary Key uniquely identifies every row in a table. A table can have only one primary key constraint, although that key may consist of multiple columns.

Column-Level Syntax:

column_name datatype
CONSTRAINT constraint_name PRIMARY KEY;

Table-Level Syntax:

CONSTRAINT constraint_name
PRIMARY KEY (column_name1, column_name2);

Example

emp_id   Name    Birth_year   Dept_id
10001    Raja    2000         001
10002    Tiya    2001         002
10001    Rohit   2002         003    Invalid

The third record contains a duplicate emp_id. If emp_id is the primary key, the DBMS will reject the duplicate value.

Unique Key Constraint

The UNIQUE constraint ensures that values in a column or combination of columns are not duplicated.

Unlike a primary key, a table can have multiple UNIQUE constraints. NULL handling can vary by DBMS, so the exact behavior should be checked for the database system being used.

Column-Level Syntax:

column_name datatype
CONSTRAINT constraint_name UNIQUE;

Table-Level Syntax:

CONSTRAINT constraint_name
UNIQUE (column_name1, column_name2);

Primary Key vs Unique Key

Aspect	Primary Key	Unique Key
Number per table	Only one primary key constraint	Multiple UNIQUE constraints can be defined
Duplicate values	Not allowed	Not allowed
NULL values	Not allowed	NULL handling depends on the DBMS
Main purpose	Uniquely identifies each row	Ensures uniqueness of specified column values

YT:- DecodeIT

Why are Integrity Constraints Important?

Integrity constraints play an important role in maintaining database quality. They provide an automatic layer of protection against invalid and inconsistent data.

Some major benefits include:

Data Accuracy: Prevents invalid values from being stored.
Data Consistency: Keeps related data consistent across tables.
Data Uniqueness: Prevents duplicate values where uniqueness is required.
Relationship Protection: Ensures that foreign key relationships remain valid.
Error Prevention: Automatically rejects operations that violate defined rules.
Database Reliability: Improves the overall quality and trustworthiness of database information.

Conclusion

DBMS integrity constraints are essential for maintaining accurate, consistent, and reliable data. They define rules that control which values can be stored and how records in different tables can be related.

The four major types of integrity constraints are Domain Constraints, Entity Integrity Constraints, Referential Integrity Constraints, and Key Constraints. Together, these constraints help prevent invalid data, duplicate records, broken relationships, and other database inconsistencies.

Understanding integrity constraints is especially important for students learning DBMS, SQL, database design, and normalization, as these concepts form the foundation of reliable relational database systems.

Keywords

DBMS Integrity Constraints, Integrity Constraints in DBMS, Domain Constraints, Entity Integrity, Referential Integrity, Key Constraints, Primary Key, Unique Key, Foreign Key, CHECK Constraint, NOT NULL Constraint, SQL Constraints, Database Integrity, DBMS Tutorial

DBMS Integrity Constraints – Ensuring Reliable Data in Relational Databases