next up previous
Next: Triggers Up: Integrity Constraints Previous: Referential Integrity in SQL


  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.
  4. An assertion in DQL-92 takes the form,

     create assertion  assertion-name check predicate

  5. Two assertions mentioned above can be written as follows.

    Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch.

     create assertion  sum-constraint check

    (not exists (select * from branch

    where (select sum)amount) from loan

    where (loan.bname = branch.bname >=

    (select sum)amount) from account

    where (account.bname = branch.bname)))

  6. Ensuring every loan customer keeps a minimum of $1000 in an account.

     create assertion  balance-constraint check

    (not exists (select * from loan L

    (where not exists (select *

    from borrower B, depositor D, account A

    where = and B.cname = D.cname

    and D.account# = A.account# and A.balance >= 1000 )))

  7. When an assertion is created, the system tests it for validity.

    If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated.

    This testing may result in significant overhead if the assertions are complex. Because of this, the assert should be used with great care.

  8. Some system developer omits support for general assertions or provides specialized form of assertions that are easier to test.

Osmar Zaiane
Tue Jun 9 15:12:55 PDT 1998