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.

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)))

LEAVE A REPLY

Please enter your comment!
Please enter your name here