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:
create assertion sum-constraint check
(not exists (select * from branch
where (
select sum(amount) from loan
where loan.branch-name =
branch.branch-name)
>= (select sum(amount) from account
where loan.branch-name = branch.branch-name)))
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.