Integrity Constraints

Integrity Constraints in Relational Databases

Integrity constraints are rules or conditions defined on a database schema that ensure the accuracy, consistency, and reliability of the data within the database. They enforce data integrity by restricting the types of data that can be stored in the database, ensuring that the data adheres to business rules and avoids invalid data entries.

There are several types of integrity constraints that can be applied to relational databases. Each of these constraints serves a specific purpose to maintain the correctness and validity of data.

Types of Integrity Constraints

1. Domain Integrity
2. Entity Integrity
3. Referential Integrity
4. User-Defined Integrity

Each of these integrity constraints is described in detail below:

---

1. Domain Integrity

Domain integrity ensures that the values in a column (attribute) are valid according to the defined domain or data type of the attribute. A domain is the set of valid values that an attribute can take. This constraint ensures that every value entered into a field is consistent with the field's defined domain (e.g., the correct data type, range, format, etc.).

• Example: If a column is defined as Age INT, the values in the Age column should only be integers. Domain integrity would prevent entering non-numeric values, such as "abc" or a negative number, if the domain restricts Age to positive integers.

Types of domain constraints:

• Data type constraint: Specifies that the data must conform to a particular data type, such as INTEGER, VARCHAR, DATE, etc.
• Range constraint: Limits the values to a specified range. For example, an Age column could have a domain constraint to ensure the value falls between 0 and 120.
• Format constraint: Ensures the data follows a specific format, such as a date format (YYYY-MM-DD).

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT CHECK (Age BETWEEN 18 AND 100)  -- Ensures age is between 18 and 100
);

---

2. Entity Integrity

Entity integrity ensures that each row (tuple) in a relation (table) is uniquely identifiable. This is primarily done by enforcing the use of primary keys. A primary key must be unique and non-null for every row in the table, ensuring that every tuple can be uniquely identified.

• Example: In the Employees table, the EmployeeID column should have unique and non-null values to uniquely identify each employee record. No two employees can have the same EmployeeID, and no employee can have a NULL EmployeeID.

Entity integrity constraints are enforced by primary key constraints. Every table must have a primary key to ensure entity integrity.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,  -- Enforces entity integrity by requiring a unique, non-null EmployeeID
    Name VARCHAR(100),
    Age INT
);

---

3. Referential Integrity

Referential integrity ensures that relationships between tables are consistent. It is used to enforce rules between two tables that are related by a foreign key. A foreign key is an attribute in one table that references the primary key of another table. The foreign key constraint ensures that a value in the foreign key column must either be NULL or match an existing value in the referenced primary key column.

• Referential integrity constraint prevents actions that would result in orphaned records (records that reference non-existing records). This ensures that the referenced records exist in the related table, maintaining valid relationships.

Key aspects of referential integrity:

• INSERT: A foreign key value must either be NULL or exist in the referenced table.
• UPDATE: If a value in the referenced table changes (for example, a primary key value), the foreign key in the related table must also change, or it may be prevented by a constraint.
• DELETE: If a record in the referenced table is deleted, the foreign key relationship must be maintained (e.g., by cascading the delete or preventing it).

Example:

In a relational schema involving Orders and Customers, the Orders table might reference the Customers table via the CustomerID:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE  -- Ensures that when a customer is deleted, all their orders are also deleted
);

In this example:

• CustomerID is a foreign key in the Orders table, referencing the CustomerID in the Customers table.
• The ON DELETE CASCADE ensures that if a customer is deleted, all related orders in the Orders table will also be deleted, preventing orphaned records.

---

4. User-Defined Integrity

User-defined integrity refers to business rules or constraints that are specific to an application or organization. These constraints cannot be directly imposed by the database management system (DBMS), but are instead defined by the users to meet the specific needs of the organization.

For example, an organization may require that employees must have a certain number of years of experience, or that a student must have at least 18 years of age to enroll in a particular course.

• Example: A user-defined rule could enforce that the Salary of an employee must be greater than or equal to a specific threshold. While the DBMS cannot enforce this rule by itself, it can be implemented using triggers or stored procedures.

Example:

CREATE TRIGGER CheckSalary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary < 30000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be greater than or equal to 30,000';
    END IF;
END;

In this example:

• A trigger is used to ensure that any new Salary inserted into the Employees table is above a specified threshold (in this case, 30,000).

---

Summary of Integrity Constraints

Constraint	Description
Domain Integrity	Ensures that attributes contain valid data according to their domain or type.
Entity Integrity	Ensures that each tuple (row) is uniquely identifiable using a primary key.
Referential Integrity	Ensures that foreign key values in one table correspond to primary key values in another table.
User-Defined Integrity	Enforces business rules that cannot be defined by the DBMS, often implemented using triggers or stored procedures.

---

Conclusion

Integrity constraints are essential for maintaining data correctness, reliability, and consistency in relational databases. They ensure that the data adheres to predefined rules and relationships, preventing the insertion of invalid data, enforcing uniqueness, and ensuring referential consistency. The key types of integrity constraints—domain, entity, referential, and user-defined—work together to ensure the quality and accuracy of the data in a database.