Assertion in SQL

What are Assertions? 
When a constraint involves 2 (or) more tables, the table constraint mechanism is sometimes hard and results may not come as expected. To cover such situation SQL supports the creation of assertions that are constraints not associated with only one table. And an assertion statement should ensure a certain condition will always exist in the database. DBMS always checks the assertion whenever modifications are done in the corresponding table. 

Syntax – 

CREATE ASSERTION  [ assertion_name ]
CHECK ( [ condition ] );

Example –  

CREATE TABLE sailors (sid int,sname varchar(20), rating int,primary key(sid),
CHECK(rating >= 1 AND rating <=10)
CHECK((select count(s.sid) from sailors s) + (select count(b.bid)from boats b)<100) ); 

In the above example, we enforcing CHECK constraint that the number of boats and sailors should be less than 100. So here we are able to CHECK constraints of two tablets simultaneously.  

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