数据库——数据库完整性

  数据库的完整性是指数据的正确性和相容性。

1、实体完整性

  关系模型的实体完整性在create table中用primary key定义。一种是定义为列级约束条件，另一种是定义为表级约束条件。

  2、参照完整性

  在create table中用foreign key短语定义哪些列为外码，用references短语指明这些外码参照哪些表的主码。

  对于参照完整性，除了应该定义外码，海英定义外码列是否允许空值。

create table SC
( sno char(9),
    cno char(4),
    grade smallint,
    primary key(sno, cno),
        /* 在表级定义实体完整性，sno、cno都不能取空值 */
    foreign key(sno) references student(sno) on delete cascade on update cascade,
        /* 当删除student表中的元组时，级联删除sc表中相应的元组 */
        /* 当更新student表中的元组sno时，级联更新sc表中相应的元组 */
    foreign key(cno) references course(cno) on delete no action on update cascade
        /* 当删除course表中的元组造成与sc不一致时，拒绝删除 */
        /* 当更新course表中的元组cno时，级联更新sc表中相应的元组 */
);

3、用户定义的完整性

1、属性上的约束条件

1）属性上约束条件的定义

属性值限制包括：

  1）列值非空（NOT NULL）

  2）列值唯一（UNIQUE）

  3）检查列值是否满足一个条件表达式（CHECK语句）

例1：check语句的使用

create table student
(sno char(9) primary key,
    ssex char(2) check(ssex in ('男', '女'）），
    grade smallint check(grade>=0 and grade<=100)
);

4、完整性约束命名子句

例1：使用constraint子句

create table student
(sno numeric(6) constraint C1 check(sno between 90000 and 99999),
    sname char(20) constraint C2 not null,
    sage numeric(3) constraint C3 check(sage<30),
    ssex char(2) constraint C4 check(ssex in('男', '女')),
    constraint studentKey primary key(sno)
);

例2：修改表中完整性限制

alter table student
drop constraint C4;


alter table student
add constraint C1 check(sno between 900 and 999);

5、断言

  通过声明性断言来指定更具一般性的约束。

  断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何断言不为真值的操作都会被拒绝执行。

例1：限制数据库课程最多60名学生选修

create assertion asse_sc_db_num
    check(600>=(select count(*)
                from course,sc
                where sc.cno=course.cno and course.cname='数据库')
);

例2：限制每一门课程最多60名学生选修

create assertion asse_sc_cnum1
    check(60>=(select count(*)
                from sc
                group by cno)
);

例3：删除断言

drop assertion <断言名>;

6、触发器

  触发器是用户定义在关系表上的一类由事件驱动的特殊过程。

  触发器又叫做事件-条件-动作规则。

  触发条件：

   1）行级触发器 for each row

    2）语句级触发器 for each statement 

例1：当对表sc的grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U（sno,cno,oldgrade,newgrade）中，其中oldgrade是修改前分数，newgrade是修改后分数。

create trigger sc_T
after update of grade on sc    /* 触发事件 */
referencing
    oldrow as oldTuple,
    newrow as newTuple
for each row                    /* 行级触发器 */
when(newTuple.grade>=1.1*oldTuple.grade)        /* 触发条件 */
    insert into sc_u(sno, cno, oldgrade, newgrade)
    values(oldTuple.sno, oldTuple.cno, oldTuple.grade, newTuple.grade)

例2：将每次对表student的插入操作所增加的学生个数记录到表studentInsertLog中。

create trigger student_Count
after insert on student
referencing
    new table as delta
for each statement
    insert into studentInsertLog(numbers)
    select count(*) from delta

例3：定义一个before行级触发器，为教室表teacher定义完整性规则“教师工资不得低于4000元，如果低于4000元，自动改为4000元”。

create trigger insert_Or_Update_Sal
before insert or update on teacher
referencing
    newrow as newTuple
for each row
begin
    if(newtuple.jov='教授') and (newtuple.sal<4000)
        then newtuple.sal:=4000;
    end if;
end;