DBMS Integrity Constraints – Ensuring Reliable Data in Relational Databases

DBMS Integrity Constraints

Maintaining consistent and correct data is essential in the database industry. A Database Management System (DBMS) must prevent invalid or inconsistent data entries that can corrupt relationships or produce unreliable results. This is where integrity constraints come into play.

A collection of guidelines known as integrity constraints are applied to database tables in order to preserve the accuracy, consistency, and legitimacy of the data. They ensure that the data modification operations like insertions, deletions, or updates do not compromise the integrity of the database.

Machine Learning Tutorial:-Click Here
Data Science Tutorial:-Click Here
Complete Advance AI topics:- CLICK HERE
Deep Learning Tutorial:- Click Here

What are Integrity Constraints?

Integrity constraints are predefined rules applied to table fields (columns) or relationships between tables in a DBMS. These constraints govern what kind of data is allowed, ensuring only valid and logical information is stored. Any operation that violates these constraints is automatically rejected, thus preventing accidental data corruption—even by authorized users.

These limitations can be defined in two primary ways:

• Column Constraints: Defined as part of the column definition during table creation.
• Table Constraints: Defined at the end of the CREATE TABLE statement and can apply to one or more columns.

Types of Integrity Constraints in DBMS

1. Domain Constraints

The acceptable range of values for a certain attribute is specified by domain constraints. The data type and permissible range of values for a column are specified by the domain. For example, a column storing employee age should not accept a string or a negative number.

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 (Dept_id should be numeric)

Here, the last row violates domain integrity because the Dept_id expects an integer, not a character.

Check Constraint:

Used to enforce domain-level rules by specifying a condition that values must satisfy.

Syntax (Column Level):

column_name datatype CONSTRAINT constraint_name CHECK (condition);

Syntax (Table Level):

CONSTRAINT constraint_name CHECK (condition);

NOT NULL Constraint:

Ensures that a column does not accept NULL values.

Syntax:

column_name datatype CONSTRAINT constraint_name NOT NULL;

2. Entity Integrity Constraints

Each table row can be uniquely identifiable thanks to entity integrity.This is enforced using a Primary Key, which must contain unique and non-null values.

Example:

Roll_no   Name    Birth_year
41        Akash   2009
23        Mina    2008
14        Rony    2010
NULL      Tina    2009   ← Invalid (Primary Key can't be NULL)

Since the main key field Roll_no is null in this instance, the final row violates the entity integrity constraint.

3. Referential Integrity Constraints

Consistent relationships between tables are guaranteed via referential integrity. It is enforced through foreign keys, which must either be NULL or match a primary key in the referenced table.

Example:

Students Table

Roll_no   Name     Branch_ID
11        Ronti    3
12        Priya    2
13        Puja     4     ← Invalid (Branch_ID 4 doesn't exist)

Branch Table

Branch_ID   Branch_Name
1           CSE
2           EE
3           ME

In this example, Branch_ID 4 in the Students table breaks the referential integrity because it doesn’t exist in the Branch table.

4. Key Constraints

Rows in a table can be uniquely identified with the use of key constraints.

Primary Key Constraint:

Each row is uniquely identified by a primary key, which also prevents duplicate or NULL values.

Syntax (Column Level):

column_name datatype CONSTRAINT constraint_name PRIMARY KEY

Syntax (Table Level):

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   ← Duplicate emp_id, violates primary key

Unique Key Constraint:

While allowing NULL values, a unique constraint guarantees that every value in a column or group of columns is unique.

Syntax (Column Level):

column_name datatype CONSTRAINT constraint_name UNIQUE

Syntax (Table Level):

CONSTRAINT constraint_name UNIQUE (column_name1 [, column_name2])

Primary Key vs Unique Key: Key Differences

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Download New Real Time Projects :-Click here

Conclusion

Integrity constraints are essential for preserving the quality and consistency of data in a DBMS. They serve as a protective framework that enforces rules on data values and relationships, preventing errors and ensuring meaningful data handling.

The four primary types of DBMS integrity constraints—Domain Constraints, Entity Integrity, Referential Integrity, and Key Constraints—work together to ensure that data entered into a system is both valid and reliable.

By understanding and implementing these constraints properly, organizations can significantly improve their data integrity and make their database systems more robust and trustworthy.

Stay tuned with UpdateGadh for more insightful articles on databases and technology.

integrity constraints sql
integrity constraints in dbms
referential integrity constraints
referential integrity constraints in dbms
integrity constraints in dbms geeksforgeeks
integrity constraints in dbms types
integrity constraints in dbms with example
entity integrity constraints
acid properties in dbms
normalization in dbms
integrity constraints in database
integrity constraints example