管理Oracle約束與分區(qū)表

1、1約束作用：約束用于確保數(shù)據(jù)庫數(shù)據(jù)的完整性,在oracle數(shù)據(jù)庫中,可以使用約束,觸發(fā)器和應用代碼(過程,函數(shù))3種方法實現(xiàn)數(shù)據(jù)完整性,這3種方法中,因為約束易于維護,并且具有最好的性能,所以實現(xiàn)數(shù)據(jù)完整性首選約束.分類：約束的種類有：not null,unique,primary key,foreign key,checkNot null確保字段值不能為空Unique:確保字段值唯一性Primary key,最常用的約束（主鍵約束），主鍵約束的列值不僅不能重復，也不能為NULL，注意一張表最多只能有一個主鍵約束，當定義主鍵約束后oracle自動建立一個以主鍵為關鍵字段的索引。Foreign

2、key:定義了主從表之間的關系，foreign要定義在從表上，但主表必須具有主鍵約束或唯一約束，當定義froeign key后外部鍵列的數(shù)據(jù)必須在主表的主鍵列中存在，或者為NULLCheck:用于強制表行數(shù)據(jù)必須滿足的條件，如工資表，工人工資必須在2000-5000之間約束狀態(tài)enable validate:是默認,新舊數(shù)據(jù)同時滿足約束規(guī)則enable novalidate:舊數(shù)據(jù)可以不滿足,檢新插入的數(shù)據(jù)要滿足約束disable validate:不允許在表上執(zhí)行任何DML操作,主要用在分區(qū)表,對于主鍵和唯一約事,會刪除相應的唯一索引,但約束狀態(tài)任可用disable novalidate數(shù)據(jù)

3、可不滿足約束規(guī)則,對于主鍵和唯一約事,會刪除相應的唯一索引,約束常用語句1. create table t(i number,v mubmer not null)  2. create table t(i number,v mubmer unique)  3. create table t(i number constraint pk_i primary key,v number) &#

4、160;4. create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v);  5. alter table t add constraint pk_i primary key (i)  6. alter table t modify i not null;

5、  7. alter table t add constraint t_i unique(i)(create index ind_name on t(i);  8. alter table t add constraint t_i check(i in (1,2,3,4,5);  9. alter table t disable novalidat

6、e constraint i  10. alter table t enable novalidate constraint check_i  11. alter table t drop constraint i;  12. alter table t drop primary key i; #常用的數(shù)據(jù)字典1. dba_constraints  2. d

7、ba_cons_columns  3. user_cons_columns  4. user_constraints  簡單應用檢驗當為一個表建立主鍵索時后，這個字段是否滿足約束非空，唯一性，而且自動建立一個索引，并查看當把約束狀態(tài)關閉再次插入相同的記錄,是否還能把把約束設為enable ividate狀態(tài)。1. SQL> create table t(i number constraint pk_i primary key,v number);  

8、2. SQL> insert into t values(1,2);  3. SQL> insert into t values(3,4);  4. SQL> commit;  5. SQL> select * from t;  6.          I     

9、     V  7. - -  8.          1          2  9.          3          

10、4  現(xiàn)在表中有兩條記錄，然后給它插主鍵為空或相同的值1. SQL> insert into t values('',10);  2. ERROR at line 1:  3. ORA-01400: cannot insert NULL into ("Y"."T"."I")  4. SQL> insert into

11、60;t values(1,10);  5. ERROR at line 1:  6. ORA-00001: unique constraint (Y.PK_I) violated  可以看到全部報錯，此時主鍵不能為空或重復查看是否建立索引1. SQL> select index_name from user_indexes;  2. INDEX_NAME  3. -  4. PK_I &#

12、160;把約束關閉再次做同樣的操用1. SQL> alter table t disable novalidate constraint pk_i;  2. Table altered.  3. SQL> insert into t values('',10);  4. 1 row created.  5. SQL> insert into t 

13、;values(1,10);  6. 1 row created.  7. SQL> commit;  8. Commit complete.  9. SQL> select * from t;  10.          I          V  11.

14、 - -  12.          1          2  13.          3          4  14.     &#

15、160;              10  15.          1         10  16. SQL>  select index_name from user_indexes;  

16、17. no rows selected  可見當把約束關閉后就可以何意給表插數(shù)據(jù)了，而具索引也自動刪除了。現(xiàn)在激活約束1. SQL> alter table t enable validate constraint pk_i;  2. alter table t enable validate constraint pk_i  3. ERROR at line 1: &

17、#160;4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  因為表中主鍵有相同的值所以不能恢復到enable validate狀態(tài)了再次測試回復到enable novalidate1. SQL> alter table t enable novalidate constraint pk_i;  2. alter table 

18、;t enable validate constraint pk_i  3. ERROR at line 1:  4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  也失敗了,因為表中主鍵有了空值和相同的值,所以恢復不到enable validate狀態(tài),但enable novalidate不檢查舊數(shù)據(jù)所以應該還能恢復到enable nov

19、alidate.要想恢復到enable novalidate必須建立主鍵索引(關閉約束時自動刪除的那個索引)如下:1. SQL> create index pk_i on t(i);  2. Index created.  然后恢復到enable disvalidate,以后再插數(shù)據(jù)不能為空,主鍵也不能重復了.1. SQL> alter table t enable novalidate constraint pk_i; &

20、#160;2. Table altered.  3. SQL> insert into t values(1,14);  4. insert into t values(1,14)  5. ERROR at line 1:  6. ORA-00001: unique constraint (SYS.PK_I) violated  2.修正約束數(shù)據(jù)當給一個表作主鍵約束時,因為已存數(shù)據(jù)

21、不滿足約束規(guī)則,會提示錯誤信息,些時必須對數(shù)據(jù)進行修正要修正數(shù)據(jù)先找出不滿足約束的數(shù)據(jù)如下表,有不滿足約束的數(shù)據(jù)1. SQL> select * from t;  2.          I          V  3. - -  4.        &

22、#160; 1          2  5.          3          4  6.          15      

23、;   12  7.          15         10  如果一個表數(shù)據(jù)量多可通過如下方法查找1. SQL> alter table t drop constraint pk_i;    2. Table altered.  3.

24、SQL>conn y / 123  4. SQL> $ORACLE_HOME/rdbms/admin/utlexcpt.sql  5. Table created.  6. SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;  7. select 

25、* from t where rowid in (select row_id from exceptions)  8.          I          V  9. - -  10.        

26、60; 15         12  11.          15         10  找到了重復的記錄修正1. SQL>update t set i=10 where v=12;  2. SQL> s

27、elect * from t;  3.          I          V  4. - -  5.          1          2

28、  6.          3          4  7.          10         12  8.        

29、  15         10  再建主鍵約束1. alter table t add constraint pk_i primary key (i)  2. Table altered.  成功了!二:分區(qū)表管理作用:將在張大表的數(shù)據(jù)分布到多個表分區(qū)段,不同分區(qū)彼此獨立,從而提高了表的可用性和性能種類:范圍分區(qū),散列分區(qū)(使用HASH算法,最常使

30、用),列表分區(qū),范圍/散列組合分區(qū),范圍/列表組合分區(qū)范圍分區(qū)表創(chuàng)建范圍分區(qū)表1. create table t(v number,b number)  2. partition by range(v) (  3. partition p1 values less than ('11') tablespace test1,  4. partition p2 values less&

31、#160;than ('21') tablespace test2);  增加與刪除分區(qū)#增加分區(qū)1. alter table t add partition p3 values less than ('31') tablespace test3;  2. alter table t drop partition p3  一個

32、時間分區(qū)的例子1. alter session set nls_data_lanage=AMERICAN;  2. alter session set nls_data_format='DD-MON-YYYY' 3. create table t(v_date date,b number)  4. partition by range(v_date)(  5. partition p1 values&

33、#160;less than ('01-APR-2009') tablespace test1,  6. partition p2 values less than ('01-JUN-2009') tablespace test2);  2.散列分區(qū)表(最常用)創(chuàng)建1. create table t1(  2. v number,b number)  3. part

34、ition by hash(v)  4. (partition p1 tablespace test1,  5. partition p2 tablespace test2);  增加分區(qū)1. alter table t add partition p3 tablespace test3;  刪除分區(qū)1. alter table t drop co

35、alesce partition;  3.列表分區(qū)建列表分區(qū)1. create table t(  2. v varchar2(10),  3. b number  4. )partition by list(v)  5. (partition p1 values('a','b') tablespace test1,  6. partition p2 values(

36、'c','d') tablespace test2);  #插入數(shù)據(jù)1. SQL> insert into t values('a',10);  2. SQL> insert into t values('d',20); #注意,插入數(shù)據(jù)時第一個字段只能為a,b,c,d1. SQL> insert into t values('

37、f',30);  2. ERROR at line 1:  3. ORA-14400: inserted partition key does not map to any partition  #查詢1. select * from t;  2. select * from t partition(p1);  3. select 

38、;* from t partition(p2);  4. select * from t where v=XXX  增加分區(qū)1. alter table t add partition p3 values('31','32') tablespace test3;  刪除分區(qū)1. alter table t drop 

39、partition p3  4.范圍/散列組合分區(qū)建立散列組合分區(qū)1. create table t(  2. v number,b number)  3. partition by range(v)  4. subpartition by hash(b) subpartitions 2  5. store in (test1,test2)(  6. partition p1 

40、values less than ('11'),  7. partition p2 values less than ('21');  查詢1. select * from t;  2. select * from t partition(p1);  3. select * from t where . &

41、#160;增加主分區(qū)和子分區(qū)1. alter table t add partition p3 values less than ('31') tablespace test3;  2. alter table t modify partition p3 add subpartition;  刪除分區(qū)1. alter table t coal

42、esce partition;  2. alter table t modify partition p1 coalesce subpartition;  5.范圍/列表組合分區(qū)創(chuàng)建1. create table t(  2. v number,b number)  3. partition by range(v)  4. subpartition by list(b)

43、60;5. (partition p1 values less than ('11') tablespace test1(  6. subpartition p1_1 values('1','3'),  7. subpartition p1_2 values('5','6')  8. ),  9. partition p2 values 

44、less than ('21') tablespace test2(  10. subpartition p2_1 values('13','14'),  11. subpartition p2_2 values('15','16')  12. );  查詢1. select * from t  2. select * from

45、 t partition(p1)  3. select * from t subpartition(p1_1)  4. select * from t where .  5. select segment_name,partition_name,tablespace_name  6. from user_segments where segment_name='T'  增

46、加分區(qū)和子分區(qū)1. alter table t add partition p3 values less than ('31') tablespace test3(  2. subpartition p3_1 values('25','26'),  3. subpartition p3_2 values('22','23');  4. alter table t modify partition r3  5. add subpartition r3_3&#