Integrity Constraints in DBMS: Types, Examples & SQL Code

A database without constraints is a database waiting to break. Without rules governing what data can be stored and how tables relate to each other, any user or application could insert NULL primary keys, reference records that do not exist, or store negative ages and invalid email formats - and the database would accept all of it silently.

Integrity constraints in DBMS are the rules that prevent this. They define the conditions every piece of data must satisfy before it can be stored, updated, or deleted. The DBMS enforces these rules automatically - no application code, no manual checks, no silent errors slipping through.

This guide covers all six types of integrity constraints in DBMS with precise definitions, SQL code for each, violation examples shown in table form, the full referential integrity options (CASCADE, SET NULL, RESTRICT), integrity constraints over relations, real-world applications, and a best practices checklist. Understanding keys in DBMS is a strong prerequisite here, since primary keys and foreign keys are at the heart of entity and referential integrity. —END MARKDOWN BLOCK---

Who This Guide Is For

What are Integrity Constraints in DBMS?

Integrity constraints in a DBMS are a set of rules defined at the schema level that restrict the values that can be stored in a database to ensure data accuracy, consistency, and reliability. They act as gatekeepers - every INSERT, UPDATE, and DELETE operation is validated against the defined constraints before it is allowed to proceed. If a constraint is violated, the operation is rejected and an error is raised.

Integrity constraints serve three core functions: they prevent invalid data from entering the database, they maintain consistency between related tables, and they enforce the business rules of the application at the database layer rather than relying on application code alone.

The importance of understanding integrity constraints is highlighted in Board Infinity's guide on ACID properties in DBMS - the Consistency property of a transaction is fundamentally about ensuring that integrity constraints are satisfied before and after every transaction.

Types of Integrity Constraints in DBMS

There are six types of integrity constraints in DBMS. The first four - domain, entity, referential, and key constraints - are the foundational types covered in most courses and exams. Assertion and triggers are the advanced constraint mechanisms that enforce complex, multi-table rules.

1. Domain Constraints

Domain constraints define the valid set of values for an attribute - the data type, acceptable range, and format that every value in a column must conform to. They are the most fundamental constraint type and operate at the column level.

Every column in a relational database has a domain - the set of all permissible values. A domain might be defined as "positive integers only", "date values between 2000 and today", or "one of three specific string values". The DBMS rejects any value that falls outside this domain.

The two primary SQL mechanisms for domain constraints are the NOT NULL constraint and the CHECK constraint.

2. Entity Integrity Constraints in DBMS

Entity integrity constraints ensure that every row in a table can be uniquely and unambiguously identified. The rule is simple but absolute: a primary key attribute can never be NULL and must always be unique.

The reason is fundamental - the primary key is the only mechanism by which a specific row can be reliably located and referenced. If a primary key were NULL, the DBMS would have no way to distinguish that row from any other NULL-keyed row. If a primary key were duplicated, operations like joins and foreign key references would return multiple rows when only one was intended - corrupting results silently.

Entity integrity is enforced through the PRIMARY KEY constraint, which automatically combines the NOT NULL and UNIQUE requirements in a single declaration.

The violation of entity integrity is one of the most common causes of data quality problems in poorly designed databases. Consider a university database where two students share the same Student_ID - every query that uses Student_ID to join with the Enrollment or Marks table would return combined results for both students, producing incorrect grade reports, incorrect fee calculations, and incorrect attendance records.

3. Referential Integrity Constraints in DBMS

Referential integrity constraints ensure that relationships between tables remain consistent. Specifically, every value in a foreign key column must either match a value in the referenced primary key column of the parent table, or be NULL (when the relationship is optional).

A foreign key creates a logical link between two tables. The table containing the foreign key is called the child table (or referencing table). The table whose primary key is being referenced is called the parent table (or referenced table). Referential integrity ensures that no child record can reference a parent record that does not exist - this would be an orphan record.

Understanding foreign keys in DBMS is the key prerequisite for this constraint type - the foreign key is both the mechanism and the subject of referential integrity.

Referential Integrity Action Options

When a row in the parent table is deleted or its primary key is updated, the DBMS needs to know what to do with the referencing rows in the child table. SQL provides four options for both ON DELETE and ON UPDATE.

4. Key Constraints

Key constraints ensure that certain columns or combinations of columns uniquely identify each row in a table. While entity integrity specifically governs the primary key, key constraints cover all types of keys - primary keys, candidate keys, unique keys, and composite keys.

Key constraints prevent duplicate and ambiguous records. Without them, a table could have two employees with the same employee ID, two customers with the same email address, or two products with the same SKU - making reliable data retrieval impossible.

5. Assertion

An assertion is a named, declarative integrity constraint that applies to the entire database rather than to a single table or column. It uses a predicate (a boolean condition) that must be true at all times across the database. If any transaction causes the assertion to become false, the transaction is rejected.

Assertions are the mechanism for expressing integrity constraints over relations in DBMS - complex business rules that span multiple tables and cannot be expressed as a simple NOT NULL, CHECK, or FOREIGN KEY. They are defined using the CREATE ASSERTION statement in SQL-92.

6. Triggers

A trigger is a procedural database object that is automatically executed in response to specific events - INSERT, UPDATE, or DELETE - on a table. Triggers are used to enforce complex integrity constraints that cannot be expressed using standard declarative constraints, implement business rules, and maintain audit trails.

Unlike static constraints (which simply reject violations), triggers can take corrective action - logging the violation, sending a notification, rolling back the transaction, or automatically updating related data. This makes triggers the most flexible integrity enforcement mechanism in DBMS.

Integrity Constraints Over Relations in DBMS

Integrity constraints over relations refers to constraints that operate across multiple tables or across multiple rows within the same table - not just at the single-row or single-column level. These are the most powerful and complex constraints in DBMS because they enforce rules that span the entire relational schema.

The three mechanisms for expressing integrity constraints over relations are:

1. Referential integrity - the most common form of cross-table constraint. A foreign key value in one relation must match a primary key value in another relation. This ensures that references between tables are always valid.

2. Assertions - allow any arbitrary SQL predicate to be enforced as a global constraint across the entire database. Since assertions can reference multiple tables in subqueries, they can express any multi-table rule.

3. Triggers - procedural enforcement of cross-table rules. When a constraint over relations is too complex for a declarative assertion, or when the DBMS does not support assertions, triggers provide the same enforcement with more granular control over timing and actions.

The distinction between single-relation and multi-relation constraints is important in exam contexts. Domain constraints, NOT NULL, and CHECK (without subqueries) apply to a single relation. FOREIGN KEY, assertions, and triggers enforce integrity over relations - across two or more tables.

Common Violations and How to Resolve Them

Real-World Applications of Integrity Constraints

Best Practices for Using Integrity Constraints

Summary: All Integrity Constraints at a Glance

Conclusion

Integrity constraints in DBMS are not optional add-ons - they are the structural backbone of a reliable relational database. Without them, every INSERT, UPDATE, and DELETE becomes a potential data quality risk. With them, the DBMS itself becomes the enforcer of business rules, making data accuracy automatic rather than dependent on application code.

The three things to take away: first, every table needs at minimum entity integrity (PRIMARY KEY) and domain constraints (NOT NULL and CHECK) to be trustworthy. Second, referential integrity (FOREIGN KEY with ON DELETE/UPDATE options) is what keeps multi-table databases consistent - always define the cascade behaviour explicitly. Third, when standard constraints cannot express a rule, assertions and triggers provide the power to enforce any condition across the entire database.

For GATE and university exams, the most frequently tested points are: the difference between entity integrity and referential integrity, the four ON DELETE/ON UPDATE action options, why a primary key cannot be NULL, and the definition of an assertion. Board Infinity's guide on normalisation in DBMS is the natural next step - normalisation theory directly uses integrity constraints (especially functional dependencies and keys) as the foundation for designing schemas that minimise redundancy and anomalies.

Frequently Asked Questions

Q1. What are integrity constraints in DBMS? Integrity constraints in DBMS are rules defined at the schema level that restrict the data values and relationships allowed in a database. They are enforced automatically by the DBMS on every INSERT, UPDATE, and DELETE operation to ensure data accuracy, consistency, and reliability.

Q2. What are the types of integrity constraints in DBMS? There are six types: domain constraints (valid data types and value ranges), entity integrity constraints (primary key must be unique and not null), referential integrity constraints (foreign key must match a primary key or be null), key constraints (uniqueness across columns), assertions (global multi-table predicates), and triggers (procedural event-driven rules).

Q3. What is entity integrity constraint in DBMS? Entity integrity ensures that every row in a table can be uniquely identified. It requires that the primary key column(s) of a table are never NULL and always contain unique values. This is enforced using the PRIMARY KEY constraint in SQL.

Q4. What is referential integrity constraint in DBMS? Referential integrity ensures that foreign key values in a child table always match an existing primary key value in the parent table, or are NULL. It prevents orphan records - child rows that reference non-existent parent rows. It is enforced using the FOREIGN KEY ... REFERENCES constraint with ON DELETE and ON UPDATE action options.

Q5. What are integrity constraints over relations in DBMS? Integrity constraints over relations are constraints that apply across multiple tables rather than to a single column or row. The primary mechanisms are referential integrity (FOREIGN KEY across tables), assertions (CREATE ASSERTION with multi-table predicates), and triggers (procedural enforcement of cross-table rules).

Q6. What is the difference between domain constraint and entity integrity constraint? Domain constraints apply to individual columns and restrict the type and range of values they can hold (NOT NULL, CHECK). Entity integrity applies to the primary key of a table and ensures it is always unique and never NULL. Domain constraints govern what data is valid; entity integrity governs whether each row can be uniquely identified.

Q7. What is an assertion in DBMS? An assertion is a global integrity constraint that defines a condition (predicate) which must always be true across the entire database, potentially spanning multiple tables. It is declared using CREATE ASSERTION in SQL. Because assertions are evaluated after every relevant DML operation, they can impose significant overhead and are rarely supported in full by mainstream databases like MySQL.

Q8. What happens when an integrity constraint is violated? When a DML operation (INSERT, UPDATE, DELETE) violates an integrity constraint, the DBMS rejects the operation and raises an error. The transaction is rolled back to its state before the violating operation. No partial writes are allowed - the constraint violation prevents the operation from completing at all.

Further Reading

Board Infinity Guides:

• Keys in DBMS
• ACID Properties in DBMS with Examples
• Normalisation in DBMS with Normal Forms
• Functional Dependency in DBMS
• DDL and DML Commands in SQL
• Structure of DBMS with a Diagram
• Relational Model in DBMS
• Deadlock in DBMS

External Resources:

• MySQL Docs - Constraints - official MySQL documentation covering all constraint types with syntax, behaviour, and edge cases
• PostgreSQL Docs - Constraints - PostgreSQL's complete constraint reference including check constraints, unique, primary and foreign keys, and exclusion constraints
• IBM Db2 Docs - Integrity Constraints - enterprise-grade documentation explaining how integrity constraints are enforced in production database environments