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 新
數(shù)據(jù)應(yīng)用規(guī)則，舊數(shù)據(jù)不管
5.enable constraints
sql> alter table xay enable validate constraint xay_id; #enable validate 新數(shù)據(jù)
應(yīng)用規(guī)則，舊數(shù)據(jù)也要檢查
同樣還有：disable novalidate, disable validate
6.disable constraints
sql> alter table sales disable constraint fk_1
sql> truncate table sales
7.using the exceptions table
#生效約束時(shí)將不符合約束條件的記錄寫入到exceptions table,反復(fù)檢查，直至沒(méi)有錯(cuò)誤
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;