文章內(nèi)容

Oracle 數(shù)據(jù)完整性的維護(hù)

發(fā)布時(shí)間: 2012/9/13 17:13:31

1.刪除約束
alter table table_name drop constraint constraint_name;
2.創(chuàng)建主鍵
alter table table_name add constraint constraint_name primary key(column_name1,column_name2);   -

3.創(chuàng)建唯一約束
alter table table_name add constraint constraint_name unique(column_name1,column_name2);
4.創(chuàng)建外鍵約束
alter table table_name1 add constraint constraint_name foreign key(column_name1) references table_name2(column_name1);
enable/disable：約束/不約束新數(shù)據(jù)
novalidate/validate:不對(duì)/對(duì)老數(shù)據(jù)進(jìn)行驗(yàn)證
alter table table_name add constraint constraint_name check(column_name like 'B%') enable/disable novalidate/validate;
5.修改約束條件，延時(shí)驗(yàn)證，commit時(shí)驗(yàn)證
alter table table_name modify constraint constraint_name initially deferred;
6.修改約束條件，立即驗(yàn)證
alter table table_name modify constraint constraint_name initially immediate;
7.設(shè)置約束延遲/立即驗(yàn)證
alter session set constraints=deferred/immediate;
8.刪除表時(shí)刪除約束
drop一個(gè)有外鍵的主鍵表,帶cascade constraints參數(shù)級(jí)聯(lián)刪除
drop table table_name cascade constraints;
9.當(dāng)truncate外鍵表時(shí)，先將外鍵設(shè)為無效，再truncate;
truncate table table_name;
設(shè)約束條件無效
alter table table_name disable constraint constraint_name;
alter table table_name enable novalidate constraint constraint_name