gdufo

 

oracle 约束管理


1.define constraints as immediate or deferred

sql> alter session set constraint[s] = immediate/deferred/default;

     set constraint[s] constraint_name/all immediate/deferred;

sql> alter table add constraint ck_sales_1 initially immediate/deferred/default;
    
     alter table modify constraint ck_sales_1 initially

immediate/deferred/default;
    

2. sql> drop table table_name cascade constraints

  sql> drop tablespace tablespace_name including contents cascade constraints

3. define constraints while create a table

sql> create table xay(id number(7) constraint xay_id primary key deferrable

sql> using index storage(initial 100k next 100k) tablespace indx);

    primary key/unique/references table(column)/check

4.enable constraints

sql> alter table xay enable novalidate constraint xay_id; #enable novalidate 新

数据应用规则,旧数据不管
5.enable constraints

sql> alter table xay enable validate constraint xay_id; #enable validate 新数据

应用规则,旧数据也要检查

同样还有:disable novalidate, disable validate

6.disable constraints

sql> alter table sales disable constraint fk_1

sql> truncate table sales

7.using the exceptions table
#生效约束时将不符合约束条件的记录写入到exceptions table,反复检查,直至没有错误

sql> start d:\xxx\utlexcpt.sql
sql> desc exceptions
sql> alter table sales add constraint ch_sales_1(qty>15)
     enable validate exceptions into exceptions

8.obtaining constraint information
  dba_constraints dba_cons_columns

sql> select constraint_name, constraint_type. deferrable,deferred, validated
     from dba_constraints where owner='HR' and table_name ='employee'

sql> select c.constraint_name, c.constraint_type,cc.column_name
     from dba_constraints c, dba_cons_columns cc
     where c.owner ='HR' and c.table_name = 'employee'
     and c.owner = cc.owner and c.constraint_name = cc.constraint_name
     order by cc.position;


 

posted on 2009-11-23 20:32 gdufo 阅读(317) 评论(0)  编辑  收藏 所属分类: Database (oracle, sqlser,MYSQL)

导航

统计

常用链接

留言簿(6)

随笔分类

随笔档案

文章分类

文章档案

收藏夹

Hibernate

友情链接

搜索

最新评论

阅读排行榜

评论排行榜