SQL ASSERTIONS

---

1. AIM

To study Assertions in SQL and implement integrity constraints using assertions.

---

2. THEORY

✔ What is an Assertion?

An Assertion is a schema-level integrity constraint in SQL that ensures a certain condition always holds true for the entire database.

Assertions:

• Are general constraints applied across multiple tables.

• Are more powerful than CHECK constraints (which apply only row-wise on one table).

• Are used when constraints involve multiple rows or multiple tables.

• Are rarely implemented in MySQL (MySQL does NOT support CREATE ASSERTION).

• Supported in SQL standard, Oracle partially, PostgreSQL using triggers.

---

✔ General Syntax (SQL Standard)

CREATE ASSERTION assertion_name
CHECK (search_condition);

Example:

CREATE ASSERTION positive_salary
CHECK (NOT EXISTS (
    SELECT *
    FROM employees
    WHERE salary < 0
));

This assertion ensures no employee has a negative salary.

---

❗ Important Note (MySQL Limitation)

MySQL does NOT support CREATE ASSERTION.
Instead, MySQL implements assertions using:

• CHECK constraints (from MySQL 8.0+)

• Triggers

• Stored procedures

So for teaching labs, we explain standard SQL assertions and provide MySQL alternative.

---

3. EXAMPLES USING SQL STANDARD ASSERTIONS

---

Example 1: Ensuring No Employee Has Negative Salary

CREATE ASSERTION positive_salary
CHECK (NOT EXISTS (
    SELECT *
    FROM employees
    WHERE salary < 0
));

---

Example 2: Ensuring Total Salary Budget Does Not Exceed 200000

CREATE ASSERTION salary_budget
CHECK (
    (SELECT SUM(salary) FROM employees) <= 200000
);

---

Example 3: Ensuring Every Course Has at Least One Student Enrolled

CREATE ASSERTION course_enrollment
CHECK (NOT EXISTS (
    SELECT *
    FROM courses c
    WHERE NOT EXISTS (
        SELECT *
        FROM enrollments e
        WHERE e.course_id = c.course_id
    )
));

---

Example 4: Prevent Insert if Stock Goes Below Zero

CREATE ASSERTION positive_stock
CHECK (
    NOT EXISTS (
        SELECT * FROM products WHERE stock < 0
    )
);

---

4. MYSQL ALTERNATIVES (FOR LAB USE)

Because MySQL doesn’t support assertions, we use triggers to simulate them.

---

Example 1: Assertion to Ensure Salary > 0 in MySQL Using Trigger

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

---

Example 2: Assertion to Prevent Budget Overflow

CREATE TRIGGER salary_budget_check
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DECLARE total INT;
    SELECT SUM(salary) INTO total FROM employees;
    IF total + NEW.salary > 200000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Budget limit exceeded!';
    END IF;
END;

---

6. PRACTICE QUESTIONS

1. Write an assertion to ensure no student's marks exceed 100.

2. Write an assertion to ensure at least one teacher is assigned to every department.

3. Write an assertion to ensure a project always has at least one employee assigned.

4. Simulate an assertion in MySQL to prevent product quantity from going negative.

5. Simulate an assertion to ensure library book count never becomes zero.

---

7. RESULT

The concept of SQL Assertions was studied and simulated using MySQL Triggers. Students learned how global constraints are enforced in database systems.