Understanding Integrity Constraints in DBMS – Types & Examples

In any relational database system, maintaining clean, reliable, and error-free data is essential for accurate operations. Integrity constraints in DBMS act as foundational rules that automatically protect your tables from duplicate entries, invalid values, and broken relationships.

Whether referred to as database integrity rules, data constraints in SQL, or DBMS data validation rules, these safeguards enforce consistency at every level of your schema. By understanding how integrity constraints in database management systems work from simple column boundaries to multi-table foreign key links, you can design robust schemas that catch human error and application bugs before bad data ever touches your system.

What is an integrity constraint?

An integrity constraint in a database management system (DBMS) is simply a set of strict rules used to ensure that the stored data remains accurate, consistent, and reliable. Think of it as a digital guardrail or filter: whenever someone tries to insert, update, or delete information, the database checks these rules first to block invalid or incomplete entries.

For example, an integrity constraint might mandate that an age field cannot contain negative numbers, a student ID must be unique so two people don’t share it, or a user must enter an email address rather than leave it blank. By continuously enforcing these standards, integrity constraints protect the database from human errors, system bugs, and duplicate entries.

Therefore, An integrity constraint (IC) is a condition that is specified on a database schema, and restricts the data that can be stored in the database.

Need for integrity constraints

The main need for integrity constraints in a DBMS is to maintain the quality, reliability, and accuracy of the stored data, preventing incorrect or corrupted information from entering the system.

Without these rules, a database could easily fill up with conflicting or nonsensical data, such as, negative bank account balances, duplicate national identity numbers, or orders linked to non-existent customers.

Integrity constraints act as automatic safeguards that catch human mistakes, software bugs, and accidental omissions before they are saved. Ultimately, they build trust in the database by guaranteeing that any application or user querying the system will retrieve dependable, consistent, and valid information every single time.

Types of integrity constraints

Integrity constraints in a Database Management System are typically divided into four main categories:

1. Domain constraints
2. Key constraints
3. Entity integrity constraints
4. Referential integrity constraints

Domain constraints

Domain constraints define the valid set of values that can be stored in a specific column. They enforce data types (e.g., Integer, Text, Date), check value ranges, and mandate whether missing data is allowed using rules like NOT NULL and CHECK.

• Data Type Enforcement: A column declared as an integer will reject text inputs.
• NOT NULL Rule: Prevents essential fields from being left blank.
• CHECK Rule: Ensures values fall within a defined range or format.

Key constraints

Key constraints enforce uniqueness among candidate keys in a table to ensure duplicate values are not stored in attributes that should be distinct. While a table can only have one Primary Key, it can have multiple UNIQUE key constraints on other columns. Unlike Primary Keys, columns with a UNIQUE constraint can allow a NULL value (depending on the SQL dialect).

• Uniqueness Enforcement: Rejects any insertion of duplicate values into a specified column.
• Candidate Key Protection: Protects business keys (e.g., email addresses, social security numbers) from duplicate entries.

Entity integrity constraints

The entity integrity constraint states that every table must have a Primary Key, and the Primary Key attribute cannot be NULL. Because the primary key serves as the unique identity of each row in a relation, allowing a NULL value would mean the system can no longer uniquely identify that record.

• No NULL Primary Keys: The database automatically rejects any INSERT or UPDATE operation that leaves a primary key field blank.
• Row Distinguishability: Ensures that no two rows are identical or unrecognizable.

Referential integrity constraints

Referential integrity constraints govern relationships between tables using Foreign Keys. This rule states that a foreign key value in the child table must either match an existing primary key value in the parent table or be completely NULL. It prevents orphaned records.

• Valid References: Cannot link a record to a non-existent parent entity.
• Cascading Operations: Deleting or updating a primary key in a parent table triggers actions (CASCADE, SET NULL, or RESTRICT) on related rows in child tables.

Example for integrity constraints

Let’s consider a university database example to understand different types of integrity constraints in DBMS. Below is the schema of the university database used in the examples:

• Department (Dept_ID, Dept_Name, Budget)
• Professor (Prof_ID, Prof_Name, Salary, Dept_ID)
• Student (Student_ID, Student_Name, Email, GPA)
• Course (Course_ID, Course_Title, Credits, Prof_ID, Dept_ID)
• Enrollment (Enrollment_ID, Student_ID, Course_ID, Grade)

Domain constraints example

In the Student table, we define domain constraints for the GPA and Student_Name attributes:

• Student_Name is set to NOT NULL. Trying to insert a student without a name will be rejected by the DBMS.
• GPA has a CHECK (GPA >= 0.0 AND GPA <= 4.0) constraint. If an administrator attempts to enter a GPA of 4.5 or -1.2, the DBMS blocks the query because the value falls outside the valid domain.

Key constraints example

In the Student table, Student_ID is the Primary Key, but Email is assigned a UNIQUE constraint.

• Student A registers with Email = ‘alice@univ.edu’.
• Student B attempts to register with Email = ‘alice@univ.edu’.
• Even though Student B has a different Student_ID, the key constraint on Email triggers a violation error and prevents duplicate account registration.

Entity integrity constraints

In the Professor table, Prof_ID is designated as the Primary Key.

• Allowed: Inserting Prof_ID = 101, Prof_Name = ‘Dr. Smith’.
• Rejected: Inserting Prof_ID = NULL, Prof_Name = ‘Dr. Jones’. The DBMS immediately rejects this operation because an entity without a primary key violates entity integrity.

Referential integrity constraints

Consider the relationship between Enrollment (child table) and Course (parent table):

• Course_ID in the Enrollment table is a foreign key referencing Course_ID in the Course table.
• If existing Course_ID values in the Course table are CS101 and MATH201:
  • Allowed: Enrolling a student with Course_ID = ‘CS101’.
  • Rejected: Trying to insert an enrollment with Course_ID = ‘ENG999’ (a course that does not exist in the Course table).
  • Blocked Deletion: Trying to delete CS101 from the Course table will be blocked by default if students are currently enrolled in it, protecting the database from broken links.

Suryateja Pericherla

Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.

He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.

He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.