ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 保持数据完整性

保持数据完整性

原创 Linux操作系统 作者:aikangyan 时间:2008-01-07 18:10:59 0 删除 编辑

通过constraint来实现数据完整性:

      完整性:域完整性(列的取值范围)、实体完整性(行的唯一性)、参照完整性

    这几种完整性可以通过三种来实现:Application Code(用户代码)、Database Trigger(触发器)、Integrity Constraint(完整性约束).

   1. Integrity Constraint是在表结构中实现的,在它被设定好之后,维护工作就由oracle来完成,因此我们优选它来实现数据完整性。

1.1 constraint的类型

    not null, unique, primary key, foreign key, check

check 可以用于所有适合where条件的地方,但是不能用在子查询中

1.1.1 删除约束条件

       alter table authors

       drop constraint ck1;

1.1.2 添加约束条件
//主键    SQL> alter table authors
                    2 add constraint pk_1 primary key(au_id);

//unique     SQL> alter table authors
                        2 add constraint uk_1 unique(au_lname, au_fname);

//外键 SQL> alter table stores
                 2 add constraint pk_sales primary key(stor_id);
              SQL> alter table sales
                   2 add constraint fk_sales foreign key(stor_id) references stores(stor_id);

//check        SQL> alter table stores
                         2 add constraint ck_stores_1 check(stor_name like 'B%')
                         3 enable novalidate   //此行表示不检验已经存在的数据,如果没有此行,已存在的数据中

                                                          //没有以B开始的stor_name,则此约束条件加不进去!系统会提示出

                                                          //错--违反检验约束条件

2. constraint 的状态

   现在         以前

disable novalidate:表中原来的数据以及新加的数据都不用约束条件来限制。相当于没有此约束条件

   disable validate:表中已经存在的数据必须满足约束条件,但是新添加数据可以不满足此约束条件

   enable novalidate:新进数据必须满足约束条件,但是在添加约束条件前,表中已经存在的数据可以不满

                                足此约束条件。

enable validate:新旧数据都要验证

缺省情况下,是enable validate!

例如:

   
SQL> alter table authors
2 add constraint ck_1 check(au_lname like 'a%')
3 enable novalidate;

表已更改。

SQL> select * from authors;

     AU_ID AU_LNAME   AU_FNAME             AU_ADDR
---------- ---------- -------------------- ----------
         1 li         dongmei              taiyuan
         2 wei        bingying             beijing
         3 hou        xiangchun            xian
         4 wang       fang                 shenzhen
         3 zeng       guoliang             shanghai
         5 li         qiang                hangzhou

已选择6行。

SQL> insert into authors
2 values(006,'bb','he','anywhere');
insert into authors
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LIDONGMEI.CK_1)

SQL> insert into authors
2 values(006,'aha','xixi','anywhere');

已创建 1 行。

但是对于新建主键、unique时就无效了,如:add constraint pk primary key(au_id) enable novalidate肯定会出错!

3. constraint检验

   分为两种:immediate--指在写完DML语句时就进行验证

                    defer--指在执行commit后才进行验证

默认条件下,会话的constraint验证类型为immediate,但是我们可以将它改为defer

      alter session set constraints = deferred;

也可以将它改回来:alter session set constraints = immediate;

例如:

        
SQL> commit
2 /

提交完成。

SQL> alter table sales
2 add constraint ck check(qty > 220)
3 initially deferred;
add constraint ck check(qty > 220)
               *
第 2 行出现错误:
ORA-02293: 无法验证 (LIDONGMEI.CK) - 违反检查约束条件


SQL> l2
2* add constraint ck check(qty > 220)
SQL> c /220/100/
2* add constraint ck check(qty > 100)
SQL> run
1 alter table sales
2 add constraint ck check(qty > 100)
3* initially deferred

表已更改。

SQL> update sales
2 set qty = 10
3 where sale_id = 4;

已更新 1 行。                                    //在不满足约束条件时,不是立即进行检验,而是在commit时进行检

                                                           //验!
SQL> commit
2 /
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 (LIDONGMEI.CK)     

当然,我们也可以把条件改为immediate

例如:

SQL>rollback

回滚已完成!
SQL> alter table sales
2 modify constraint ck
3 initially immediate;

表已更改。

SQL> insert into sales
2 values(5, 'pei',80);
insert into sales
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LIDONGMEI.CK)

4. 主键、外键

4.1 主键一旦被引用,则删除主键所在的表时,一定要使用cascade constraints,否则会出错!

SQL> alter table authors
2 add constraint pk primary key(au_id);

表已更改。

SQL> create table sales
2 (sale_id int,
3 qty number(10));

表已创建。

SQL> alter table sales
2 add constraint fk foreign key(sale_id) references authors(au_id);

表已更改。

SQL> select * from authors;

     AU_ID AU_LNAME   AU_FNAME
---------- ---------- ----------
         1 li         dongmei
         2 wei        bingying
         3 li         hua

SQL> insert into sales
2 values(1,100);

已创建 1 行。

SQL> insert into sales
2 values(4, 200);
insert into sales
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (LIDONGMEI.FK) - 未找到父项关键字

SQL> drop table authors cascade constraints;

表已删除。
SQL> insert into sales
2 values(4, 200);

已创建 1 行。

4.2 如果要truncate主键表中的值时,则必须先使disable 外键

    
SQL> truncate table authors;
truncate table authors
               *
第 1 行出现错误:
ORA-02266: 表中的唯一/主键被启用的外键引用


SQL> alter table sales
2 disable constraint fk;

表已更改。

SQL> truncate table authors;

表被截断。

5. exceptions 表

   当我们要添加约束条件时,如果表中本身已有不符合条件的数据,那么我们可以先将不符合条件的数据所在的行记录到一张表中,然后再修改这些数据使他们满足条件,最后再次运行要添加约束条件的DML语句即可。

第一步:通过运行utlexcpt.sql来创建exceptions表

  
SQL> start d:\oracle\product\10.2.0\db_3\rdbms\admin\utlexcpt.sql;

表已创建。


SQL> desc exceptions;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
ROW_ID                                             ROWID
OWNER                                              VARCHAR2(30)
TABLE_NAME                                         VARCHAR2(30)
CONSTRAINT                                         VARCHAR2(30)

第二步:找出不满足条件的行,并修改它们,使其满足约束条件!

SQL> select * from sales;

SALE_ID           QTY
---------- ----------
1                 100
2                 200
3                 110

SQL> alter table sales
2 add constraint ck check( qty > 110 )
3 enable validate
4 exceptions into exceptions;
add constraint ck check( qty > 110 )
               *
第 2 行出现错误:
ORA-02293: 无法验证 (LIDONGMEI.CK) - 违反检查约束条件


SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME
------------------ ------------------------------ --------------
CONSTRAINT
------------------------------
AAAMogAAEAAAAIoAAA LIDONGMEI                      SALES
CK

AAAMogAAEAAAAIoAAC LIDONGMEI                      SALES
CK


SQL> update sales
2 set qty = 120
3 where sale_id in ( select row_id from exceptions);
where sale_id in ( select row_id from exceptions)
      *
第 3 行出现错误:
ORA-01410: 无效的 ROWID

如果不出错的话,我们再运行

SQL> alter table sales
2 add constraint ck check( qty > 110 )
3 enable validate
4 exceptions into exceptions;
add constraint ck check( qty > 110 )

就可以正确的添加约束条件到sales中了。

6. 查找当前用户下有哪些约束

    select * from user_constraints;

查看约束条件定义在哪个column上

   select * from user_cons_columns;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12379366/viewspace-113142/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    81
  • 访问量
    43367