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 (DBMS) 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.
For example:
 Making sure every customer has a valid email address.
 Ensuring that an order in the database is always linked to an existing
customer.
These rules prevent mistakes, such as adding incomplete or incorrect data,
and make sure the database is secure and well-organized. In simple terms,
integrity constraints keep the database neat, correct and trustworthy.
Types of Integrity Constraints
Integrity Constraints
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:
Student_Id Name Semester Age

21CSE100 Aniket Kumar 6th 20

21CSE101 Shashwat Dubey 7th 21

21CSE102 Manvendra Sharma 8th 22

21CSE103 Ashmit Dubey 5th 20

This 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
 CHECK Constraint

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.
Student_id Name Semester Age

21CSE1001 Sonali Rao 5th 20

21CSE1012 Anjali Gupta 5th 21

21CSE1023 Aastha Singh 5th 22

21CSE1034 Ayushi Singh NULL 20

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.
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

NULL Mukesh 5th 20

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:
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

21CSE102 Mukesh 5th 20

It is now acceptable because all rows must be unique.

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.
Student_id Name Semester Age

101 Ramesh 5th 20

102 Kamlesh 5th 21

103 Akash 5th 22

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.

 Valid: 103.
 Invalid: A row with NULL for student_id will be rejected.

Unique Key Constraints

A Unique Key ensures that the values in a column are unique, but unlike a
primary key, it allows one NULL value.
Employee_ID Email Name

1 aniket@example.com Aniket Kumar

2 NULL Shashwat Dubey

3 shashwat@example.com Manvendra Sharma

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.
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.
Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi
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

Example 1: Audit Trail Trigger

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_audit(emp_id, old_salary, new_salary, changed_on)
VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

Explanation: Before any update on employee, insert a record into employee_audit with the
old and new salaries.

Example 2: Enforcing Business Rule

CREATE TRIGGER check_salary
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.salary < 3000 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary too low!';
END IF;
END;

Explanation: Prevents inserting employees with a salary lower than 3000.

Types of Triggers:

Type Description
BEFORE Trigger Executes before the triggering SQL statement.
AFTER Trigger Executes after the SQL statement has been executed.
INSTEAD OF Trigger Used mainly with views (common in SQL Server/Oracle).

Use Cases:

 Enforcing data integrity.

 Logging changes (audit).
 Validating input.
 Automating system tasks.
Assertion
What is an Assertion?

An assertion is a condition or predicate defined on a database schema. The DBMS

automatically checks the assertion whenever any INSERT, DELETE, or UPDATE
operation is performed. If the condition is violated, the operation is rejected.

Syntax (SQL Standard)

sql
CopyEdit
CREATE ASSERTION assertion_name
CHECK (condition);

Note: Assertions are part of the SQL standard, but not all DBMSs support them (e.g.,

MySQL does not support CREATE ASSERTION, but PostgreSQL and Oracle have ways to
emulate them using triggers).

Example 1: Total salary of employees in a department should not exceed

₹100000
sql
CopyEdit
CREATE ASSERTION total_salary_limit
CHECK (
NOT EXISTS (
SELECT dept_id
FROM employee
GROUP BY dept_id
HAVING SUM(salary) > 100000
)
);

If the total salary in any department exceeds ₹100000, this assertion prevents the
update/insert.

Example 2: Every employee’s age must be at least 18

sql
CopyEdit
CREATE ASSERTION min_employee_age
CHECK (
NOT EXISTS (
SELECT * FROM employee
WHERE age < 18
)
);
Limitations:

 Not widely supported in real-world DBMS (e.g., MySQL, SQL Server don’t
implement it directly).
 Often emulated using triggers or procedures.
 Can impact performance if too complex or checked frequently.

When to Use Assertions:

 When complex integrity constraints are needed across multiple rows or tables.
 For business rules that can’t be enforced using regular table constraints.