Assertions

1. An assertion is a predicate expressing a condition we wish the database to always satisfy.

2. Domain constraints, functional dependency and referential integrity are special forms of assertion.

3. Where a constraint cannot be expressed in these forms, we use an assertion, e.g.
   • Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch.
   • Ensuring every loan customer keeps a minimum of $1000 in an account.

   • Ensuring no account has less than $25 in it.
4. When updates are attempted, the system must test to see whether any assertions are violated, and refuse the update if so.

5. This testing may result in significant overhead if the assertions are complex. Because of this, the assert statement has disappeared from the newer versions of SQL, including the standard. A proposal exists to include general assertions in future revisions of the standard.

6. To ensure that no account balance is negative:

   

   (This could have been written as a domain constraint.)

7. To ensure no bank employee can be his or her personal banker:

   

   This cannot be written as a domain constraint.