数据库完整性约束

1. Integrity constraints 完整性约束

(1) Domain Constraints:

     – attribute values for attribute A shall be atomic values from domain(A), c.f. user defined atomic datatype

(2) Key Constraints:

    – candidate keys for a relation must be unique. The key identifies uniquely an entity (object).

(3) Entity Constraints:

    – no primary key is allowed to have a null value (the primary key identifies entities/objects)

(4) Referential Integrity Constraints:

    – a tuple that refers to another tuple in another relation must refer to an existing tuple (no dangling references to other entities)

(5) Semantic Integrity Constraints:

   – e.g. “an employee’s total work time per week can not exceed 40 hours for all projects taken all together”.

2. Delete/Update Cascades 级联删除/更新

create table account
...
foreign key (branch-name) references branch
on delete cascade
on update cascade
...)

(1) If a tuple in branch is deleted (updated), there is a tuple in account that will also be deleted (updated), i.e. the delete (update) cascades.
(2) In addition to cascade there are options of set default and set null.

3. Check and Assertion 校验和断言

(1) Tuple-based (c.f. domain based) constraints can be defined by a check(P) clause, where P is a predicate 

create table branch
(branch_name char(15) primary key,
branch_city char(30),
assets integer,
check (assets >= 0))

(2) An assertion is a predicate expressing a condition that we wish the database  always to satisfy.

/*The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch.*/
create assertion sum-constraint check
(not exists
(select *
from branch b
where (select sum(amount)
from loan
where loan.branch-name = b.branch-name)
>= (select sum(amount)
from account
where account.branch-name = b.branch-name)))