Complex Integrity Constraints in SQL With Examples

In SQL, integrity constraints are rules that ensure data integrity in a database. They can be classified into two categories: simple and complex.

Simple integrity constraints include primary keys, unique constraints, and foreign keys. These constraints ensure that data in a table is unique and consistent, and they prevent certain operations that would violate these rules.

On the other hand, complex integrity constraints are more advanced rules that cannot be expressed using simple constraints. They are typically used to enforce business rules, such as limiting the range of values that can be entered into a column or ensuring that certain combinations of values are present in a table.

There are several ways to implement complex integrity constraints in SQL, including:

1. Check Constraints: Check constraints are used to restrict the range of values that can be entered into a column. For example, a check constraint can be used to ensure that a date column only contains dates in a certain range.

CREATE TABLE Employee (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Salary DECIMAL(10,2),
    CONSTRAINT CHK_Age CHECK (Age >= 18 AND Age <= 65),
    CONSTRAINT CHK_Salary CHECK (Salary >= 0)
);
 

 In this example, we are creating a table called "Employee" with columns for ID, Name, Age, and Salary. We have added two check constraints, one to ensure that the age is between 18 and 65, and another to ensure that the salary is greater than or equal to 0.

2. Assertions: Assertions are used to specify complex rules that cannot be expressed using simple constraints. They are typically used to enforce business rules, such as ensuring that a customer's age is greater than a certain value.

CREATE TABLE Customer (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Gender VARCHAR(10),
    CONSTRAINT CHK_Age CHECK (Age >= 18),
    CONSTRAINT CHK_Gender CHECK (Gender IN ('M', 'F')),
    CONSTRAINT CHK_Female_Age CHECK (Gender <> 'F' OR Age >= 21)
);
 

In this example, we are creating a table called "Customer" with columns for ID, Name, Age, and Gender. We have added three constraints: one to ensure that the age is greater than or equal to 18, another to ensure that the gender is either "M" or "F", and a third to ensure that if the gender is "F", the age is greater than or equal to 21.

3. Triggers: Triggers are special procedures that are executed automatically in response to certain events, such as an insert or update operation on a table. Triggers can be used to implement complex business rules that cannot be expressed using simple constraints.

CREATE TABLE Order (
    ID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2),
    CONSTRAINT FK_Customer_Order FOREIGN KEY (CustomerID) REFERENCES Customer(ID)
);

CREATE TRIGGER TR_Order_Check_Amount
BEFORE INSERT ON Order
FOR EACH ROW
BEGIN
    IF NEW.Amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be greater than zero.';
    END IF;
END;
 

In this example, we are creating a table called "Order" with columns for ID, CustomerID, OrderDate, and Amount. We have added a foreign key constraint to ensure that the CustomerID in the Order table matches the ID in the Customer table. We have also created a trigger called "TR_Order_Check_Amount" that will be executed before an insert operation on the Order table. The trigger checks if the amount being inserted is greater than zero. If it is not, an error message will be generated and the insert operation will be cancelled.

These are just a few examples of how complex integrity constraints can be implemented in SQL to ensure the accuracy and consistency of data in a database.

Overall, complex integrity constraints are essential for ensuring the integrity and consistency of data in a database. They allow developers to enforce complex business rules and prevent data inconsistencies, ensuring that the data in the database remains accurate and trustworthy.