Integrity Constraints of Relationships in E-R Model

DBMS

About Lesson

Integrity Constraints:

Integrity constraints are rules defined on a database schema to maintain the accuracy, consistency, and reliability of the data stored in a database.
These constraints help ensure that data meets certain criteria or conditions, preventing the introduction of errors or inconsistencies.
There are several types of integrity constraints, commonly found in relational database management systems (RDBMS).
These Constraints are the restrictions imposed on the Database Contents and Operations.
They Ensures the Correctness of data in the Database.

Types of Integrity Constraints:

1. Domain Constraints:

Domain integrity constraints ensure that values stored in a specific column, or attribute, of a database table conform to a predefined set of allowable values or conditions. These constraints are defined at the attribute level and are used to maintain the accuracy and consistency of data within the specified domain.

Requirements:

Definition of Domain: Clearly defining the permissible values or conditions for each attribute’s domain.
Data Type Compatibility: Ensuring that the specified constraints align with the data type of the attribute.
Application of Constraints: Incorporating the constraints into the database schema.

Purpose:

Data Accuracy: Ensuring that data within a specific column adheres to the defined rules, preventing the storage of invalid or inappropriate values.
Consistency: Maintaining a uniform structure for data, which enhances the overall integrity and reliability of the database.
Data Quality: Contributing to the quality of data by enforcing constraints that reflect the business rules and requirements associated with specific attributes.

Example:

Consider a table named “Employees” with the following attributes:

EmployeeID (Primary Key)
FirstName
LastName
Gender
DateOfBirth
DepartmentID (Foreign Key)

Now, let’s focus on the “Gender” attribute and apply a domain integrity constraint:

+------------+--------------+-------------+--------+--------------+--------------+
| EmployeeID | FirstName    | LastName    | Gender | DateOfBirth  | DepartmentID |
+------------+--------------+-------------+--------+--------------+--------------+
|            |              |             |        |              |              |
|            |              |             |        |              |              |
|            |              |             |        |              |              |
+------------+--------------+-------------+--------+--------------+--------------+

In this example:

The “Gender” attribute is restricted to accept only ‘M’ (Male) or ‘F’ (Female) values.
The CHECK constraint is used to enforce this domain integrity rule.
If an attempt is made to insert a record with a different value for “Gender,” the constraint will prevent the operation, ensuring that only valid values are stored.

This domain integrity constraint helps maintain the quality and consistency of the “Gender” data within the “Employees” table by restricting it to a specific set of allowable values.

2. Entity Integrity Constraints:

Entity Integrity Constraint is a rule in a relational database that ensures that every table has a primary key, and that the primary key value is unique and not null for each record in the table. In other words, it guarantees the uniqueness and non-null nature of the primary key within a table.

Requirements:

Primary Key: Every table must have a primary key, which is a unique identifier for each record in the table.
Uniqueness: The values in the primary key column must be unique across all records in the table.
Non-null: The primary key column cannot have null values.

Purpose:

Uniqueness: Ensures that each record in the table can be uniquely identified by its primary key. This is essential for data integrity and accurate retrieval of information.
Identification: Provides a reliable and unambiguous way to identify and distinguish individual records within a table.
Referential Integrity Support: Primary keys are often used as foreign keys in other tables, establishing relationships between tables. Entity Integrity Constraints ensure the consistency and integrity of these relationships.

Example:

Let’s consider a simple “Students” table:

| StudentID |  Name    |   Age  |  GPA  |
|-----------|----------|--------|-------|
|    101    |  Alice   |   20   |  3.5  |
|    102    |  Bob     |   22   |  3.2  |
|    103    |  Charlie |   21   |  3.9  |

In this example:

StudentID is the primary key.
Each StudentID is unique (no two students have the same ID).
Each StudentID is non-null (there are no null values in the StudentID column).

The Entity Integrity Constraint is satisfied for this table because it has a primary key, and the primary key values are unique and non-null. If someone tries to insert a new record without providing a StudentID or with a StudentID that already exists in the table, it would violate the Entity Integrity Constraint, and the DBMS would reject the operation to maintain the integrity of the data.

3. Referential Integrity Constraints:

Referential Integrity (RI) constraints define the relationships between tables in a relational database. These constraints ensure that relationships between tables remain consistent and valid. The most common type of RI constraint is the Foreign Key (FK) constraint.

Requirements:

For a table to have a foreign key constraint:
- There must be a corresponding primary key in another table that the foreign key refers to.
- The data type of the foreign key must match the data type of the primary key it references.
- The foreign key column can contain NULL values unless it is explicitly declared as NOT NULL.
- There should be no orphaned records, meaning every foreign key value should correspond to a valid primary key in the referenced table.

Purpose:

Maintain consistency between related tables: RI constraints ensure that relationships between tables accurately reflect the real-world associations they model.
Prevent orphans and maintain data integrity: RI constraints prevent situations where a record in a table refers to a nonexistent record in another table (orphaned record).
Simplify data maintenance: RI constraints automate the enforcement of relationships, reducing the likelihood of errors during data modifications.

Example:

Let’s consider two tables: Orders and Customers.


+------------+----------------+
| CustomerID | CustomerName   |  
+------------+----------------+
|    1       | CustomerA      |
|    2       | CustomerB      |
|    3       | CustomerC      |
+------------+----------------+

+---------+------------+------------+
| OrderID | CustomerID | OrderDate   |
+---------+------------+------------+
|   101   |      1     | 2024-01-01 |
|   102   |      2     | 2024-01-02 |
|   103   |      1     | 2024-01-03 |
+---------+------------+------------+

In this example:

The Customers table has a primary key CustomerID.
The Orders table has a primary key OrderID and a foreign key CustomerID that references the CustomerID column in the Customers table.
The foreign key constraint ensures that every value in the Orders.CustomerID column corresponds to a valid Customers.CustomerID.
If an attempt is made to insert an order with a CustomerID that does not exist in the Customers table, or if a customer is deleted and has associated orders, the foreign key constraint will prevent such actions, maintaining referential integrity.

his example illustrates how referential integrity constraints help establish and maintain meaningful relationships between tables in a relational database.

4. Key Integrity Constraints:

Key integrity constraints are a subset of integrity constraints that specifically deal with the uniqueness and relationships between keys in a relational database. The primary key and foreign key constraints are two key integrity constraints commonly used to ensure data accuracy and consistency.

a. Primary Key Constraint:

Definition:

The primary key constraint ensures that each row in a table is uniquely identified by a specific column or set of columns, and it does not allow for duplicate or null values in those columns.

Requirements:

The columns designated as the primary key must contain unique values.
The primary key columns cannot contain null values.

Purpose:

The primary key uniquely identifies each record in a table, facilitating data retrieval, referencing, and maintaining data integrity. It also serves as the basis for defining relationships with other tables through foreign keys.

Example:

Consider a “Students” table with the following structure:

| StudentID |   Name   |   Age   |  Major   |
|-----------|----------|---------|----------|
|    101    |  Alice   |   20    |  Math    |
|    102    |  Bob     |   22    |  Physics |
|    103    |  Carol   |   21    |  English |

In this example:

Here, “StudentID” could be designated as the primary key, ensuring each student has a unique identifier.

B. Foreign Key Constraint:

Definition:

The foreign key constraint establishes a relationship between two tables, where the values in one table’s column (foreign key) match the values in another table’s primary key column.

Requirements:

The foreign key column values must match the primary key column values in the referenced table.
Foreign key values can be null unless the foreign key column is part of the primary key (composite foreign key).

Purpose:

The foreign key constraint ensures referential integrity between related tables, preventing the creation of orphaned records and maintaining consistency in the data.

Example:

Consider a “Courses” table with the following structure:

| CourseID |    Title     | Instructor |
|----------|--------------|------------|
|   201    |  Calculus I   |  Dr. Smith |
|   202    |  Physics I    |  Dr. Jones |
|   203    |  English 101  |  Prof. Lee  |

If the “Students” table has a foreign key column “CourseID” referencing the “Courses” table, it might look like this:

| StudentID |   Name   |   Age   |  Major   | CourseID |
|-----------|----------|---------|----------|----------|
|    101    |  Alice   |   20    |  Math    |   201    |
|    102    |  Bob     |   22    |  Physics |   202    |
|    103    |  Carol   |   21    |  English |   203    |

Here, “CourseID” in the “Students” table is a foreign key referencing the “Courses” table’s primary key, establishing a relationship between the two tables.