DBMS Integrity Constraints

Integrity constraints are a set of rules used in DBMS to ensure that the data in a database is accurate, consistent and reliable. These rules helps in maintaining the quality of data by ensuring that the processes like adding, updating or deleting information do not harm the integrity of the database. Integrity constraints also define how different parts of the database are connected and ensure that these relationships remain valid. They play an essential role in making sure the data is meaningful and follows the logical structure of the database.

What are Integrity Constraints ?

Integrity constraints in a Database Management System are rules that help keep the data in a database accurate, consistent and reliable. They act like a set of guidelines that ensure all the information stored in the database follows specific standards.

Example: Making sure every customer has a valid email address & ensuring that an order in the database is always linked to an existing customer.

Note: These rules prevent mistakes, such as adding incomplete or incorrect data, and make sure the database is secure and well-organized.

Types of Integrity Constraints

There are Different types of Integrity Constraints used in DBMS, these are:

1. Domain Constraints
2. Entity Integrity Constraints
3. Key Constraints
4. Referential integrity constraints
5. Assertion
6. Triggers

1. Domain Constraints

Domain constraints are a type of integrity constraint that ensure the values stored in a column (or attribute) of a database are valid and within a specific range or domain. In simple terms, they define what type of data is allowed in a column and restrict invalid data entry. The data type of domain include string, char, time, integer, date, currency etc. The value of the attribute must be available in comparable domains.

Example: Below table demonstrates domain constraints in action by enforcing rules for each column

1. Student_Id: Must be unique and follow a specific format like 21CSE###. No duplicates or invalid formats allowed.
2. Name: Accepts only valid text (no numbers) and cannot be left empty (NOT NULL constraint).
3. Semester: Allows specific values like 5th, 6th, etc., and ensures valid input (e.g., no 10th if not permitted).
4. Age: Must be an integer within a reasonable range (e.g., 18-30) and cannot contain invalid data like negative numbers or text.

Types of Domain Constraints:

• NOT NULL Constraint: Ensures No records can have NULL value.
• CHECK Constraint: This Constraint Checks for any specified condition over any attribute.

Why Domain Constraints Are Important :

• They prevent invalid or inconsistent data from entering the database.
• They ensure the database is reliable and follows predefined business rules.
• They make the database easier to manage and maintain by reducing errors.

Example: Let, the not-null constraint be specified on the "Semester" attribute in the relation/table given below, then the data entry of 4th tuple will violate this integrity constraint, because the "Semester" attribute in this tuple contains null value. To make this database instance a legal instance, its entry must not be allowed by database management system.

Read more about Domain Constraints and its types, Here.

2. Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value because primary key is used to determine individual rows in a relation uniquely, if primary key contains null value then we cannot identify those rows. A table can contain null value in it except primary key field.

Key Features of Entity Integrity Constraints:

• Uniqueness: The primary key value must be unique for each row in the table. No duplicate entries are allowed in the primary key column.
• NOT NULL: The primary key column cannot contain NULL values, as every row must have a valid identifier.
• Essential for Table Design: Ensures that every record in the table can be uniquely identified, preventing ambiguity.

Example: It is not allowed because it is containing primary key (Student_id) as NULL value.

3. Key Constraints

Key constraints ensure that certain columns or combinations of columns in a table uniquely identify each row. These rules are essential for maintaining data integrity and preventing duplicate or ambiguous records.

Why Key Constraints Are Important ?

• Prevent Duplicates: Ensure unique identification of rows.
• Maintain Relationships: Enable proper linking between tables (via foreign keys).
• Enforce Data Integrity: Prevent invalid or inconsistent data.

Example: It is now acceptable because all rows must be unique.

3.1 Primary Key Constraints

It states that the primary key attributes are required to be unique and not null. That is, primary key attributes of a relation must not have null values and primary key attributes of two tuples must never be same. This constraint is specified on database schema to the primary key attributes to ensure that no two tuples are same.

Example: Here, in the below example the Student_id is the primary key attribute. The data entry of 4th tuple violates the primary key constraint that is specifies on the database schema and therefore this instance of database is not a legal instance.

• Unique Values: Each student_id must be unique. 101, 102, 103 are valid. Inserting 101 again would result in an error.
• Not NULL: student_id cannot be NULL.
• Invalid: A row with NULL for student_id will be rejected.

3.2 Unique Key Constraints

The Unique key constraint in DBMS ensures that all values in a specified column (or group of columns) are distinct across the table. It prevents duplicate entries, maintaining data integrity, but unlike the primary key, it allows one NULL value.

Example: Here, in the below example the Email column has NULL value in 2nd record.

• Unique Values: The email column must contain unique values. aniket@example.com and shashwat@example.com are valid. Adding another row with aniket@example.com would result in an error.
• Allows One NULL: The email column can contain one NULL value.
• Valid: NULL in the second row.
• Invalid: Adding another row with NULL in email will be rejected.

4. Referential integrity constraints

Referential integrity constraints are rules that ensure relationships between tables remain consistent. They enforce that a foreign key in one table must either match a value in the referenced primary key of another table or be NULL. This guarantees the logical connection between related tables in a relational database.

Why Referential Integrity Constraints Are Important ?

• Maintains Consistency: Ensures relationships between tables are valid.
• Prevents Orphan Records: Avoids cases where a record in a child table references a non-existent parent record.
• Enforces Logical Relationships: Strengthens the logical structure of a relational database.

Example: Here, in below example Block_No 22 entry is not allowed because it is not present in 2nd table.

To read about SQL FOREIGN KEY Constraint Refer, Here.

5. Assertion

An assertion is a declarative mechanism in a database that ensures a specific condition or rule is always satisfied across the entire database. It is a global integrity constraint, meaning it applies to multiple tables or the entire database rather than being limited to a single table or column. An assertion in SQL-92 takes the form:

create assertion <assertion-name> check <predicate>

When an assertion is made, the system tests it for validity. This testing may introduce a significant amount of overhead; hence assertions should be used with great care.

Example of an Assertion:

CREATE ASSERTION sum_constraint
CHECK (
NOT EXISTS (
SELECT *
FROM branch
WHERE (
SELECT SUM(amount)
FROM loan
WHERE loan.branch_name = branch.branch_name
) >= (
SELECT SUM(amount)
FROM account
WHERE account.branch_name = branch.branch_name
)
)
);

Explanation:

The following SQL statement creates an assertion to ensure that the total loan amount at each branch is always less than the total account balances at the same branch.

• Purpose: This assertion enforces a global business rule. The sum of all loan amounts for a branch must always be less than the sum of all account balances in the same branch. This prevents branches from issuing loans beyond their financial capacity.
• Subqueries: The first subquery (SELECT SUM(amount) FROM loan) calculates the total loan amount for each branch. The second subquery (SELECT SUM(amount) FROM account) calculates the total balance of accounts for the same branch.
• Condition: The NOT EXISTS clause ensures there is no branch where the loan amount is greater than or equal to the account balance.
• Behavior: If a transaction (e.g., inserting a loan or updating an account) violates this rule, the operation will be rejected by the database.

6. Triggers

A trigger is a procedural statement in a database that is automatically executed in response to certain events such as INSERT, UPDATE, or DELETE. Triggers are often used to enforce complex integrity constraints or implement business rules that cannot be captured using standard constraints like primary keys or foreign keys.

Example SQL Trigger:

CREATE TRIGGER handle_overdraft
AFTER UPDATE ON account
FOR EACH ROW
BEGIN
-- Check if the balance has become negative after the update
IF NEW.balance < 0 THEN
-- Set the account balance to zero
UPDATE account
SET balance = 0
WHERE account_number = NEW.account_number;

-- Create a loan record with the same account number as the loan number
INSERT INTO loan (loan_number, loan_amount)
VALUES (NEW.account_number, ABS(NEW.balance)); -- ABS to ensure positive loan amount
END IF;
END;

Explanation:

• Trigger Type: The trigger runs after an update on the account table.
• Condition: It checks if the account balance is negative after the update.
• Actions: If the balance is negative, the account balance is reset to zero. A loan is created with the same account number as the loan number, and the loan amount is the absolute value of the negative balance. The ABS() function ensures the loan amount is positive.