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;