In SQL, an assertion is a statement that ensures that certain conditions are always enforced in the database no matter what. Same as domain or other constraints, assertions differ in the way that they are defined separately from table definitions. It is a named statement that defines a condition that must be true for the data in the database. If the condition is not met, the assertion will fail and the database will generate an error.
Here is the syntax for creating an assertion in SQL:
CREATE ASSERTION assertion_name
CHECK (condition);For example, to create an assertion called “salary_assertion” that checks that no employee in the “employees” table has a salary greater than $100,000, you could use the following statement:
CREATE ASSERTION salary_assertion
CHECK (salary <= 100000);We can create different assertions for specific conditions that must always satisfy in SQL. For example, the department_id attribute in manager relation is always not null since each manager works at least in one department. The following example shows an assertion named nomanager which checks that all the tuples in manager relation with department_id being NULL as not a manager.
CREATE ASSERTION nomanager CHECK ( NOT EXISTS ( SELECT * FROM MANAGER WHERE Department_id IS NULL ) );
Hence, the above assertion ensures that there is no manager who is not assigned any department at any time.
Another example can be the sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. This can be satisfied by creating assertion as below:
NOT EXISTS ( <– Start Bad-Data Filter
SELECT 1 FROM branch B
WHERE ( <– Start Loans Calculation
SELECT SUM(amount) FROM loan L
WHERE L.branch_name = B.branch_name
) <– End Loans Calculation
>= ( <– Start Accounts Calculation
SELECT SUM(amount) FROM account A
WHERE A.branch_name = B.branch_name
) <– End Accounts Calculation
) <– End Bad-Data Filter
); <– End Global Check
To drop an assertion, you can use the following statement:
DROP ASSERTION assertion_name;Assertions are useful for enforcing data integrity and ensuring that the data in the database meets certain conditions. They can be used to enforce business rules or to ensure the consistency of the data. However, they can also be time-consuming to create and maintain, so they are not always used in practice.
CHECK vs ASSERTION
SQL assertions provide a way to define rules that the database should always maintain. Although they are not commonly supported in many popular database systems, understanding the concept is useful for learning database constraints and integrity rules. For beginners, assertions also help build a stronger understanding of how databases can enforce business rules beyond basic constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK. Even when assertions are unavailable in a particular DBMS, the same requirements can often be implemented using supported constraints, triggers, or application-level validation. Happy Learning! – DataSagar
