1．約束

作用：

約束用于確保數(shù)據(jù)庫(kù)數(shù)據(jù)的完整性,在oracle數(shù)據(jù)庫(kù)中,可以使用約束,觸發(fā)器和應(yīng)用代碼(過(guò)程,函數(shù))3種方法實(shí)現(xiàn)數(shù)據(jù)完整性,這3種方法中,因?yàn)榧s束易于維護(hù),并且具有***的性能,所以實(shí)現(xiàn)數(shù)據(jù)完整性***約束.

分類(lèi)：

約束的種類(lèi)有：not null,unique,primary key,foreign key,check

Not null確保字段值不能為空

Unique:確保字段值唯一性

Primary key,最常用的約束（主鍵約束），主鍵約束的列值不僅不能重復(fù)，也不能為NULL，注意一張表最多只能有一個(gè)主鍵約束，當(dāng)定義主鍵約束后oracle自動(dòng)建立一個(gè)以主鍵為關(guān)鍵字段的索引。

Foreign key:定義了主從表之間的關(guān)系，foreign要定義在從表上，但主表必須具有主鍵約束或唯一約束，當(dāng)定義froeign key后外部鍵列的數(shù)據(jù)必須在主表的主鍵列中存在，或者為NULL

Check::用于強(qiáng)制表行數(shù)據(jù)必須滿足的條件，如工資表，工人工資必須在2000-5000之間

約束狀態(tài)

enable validate:是默認(rèn),新舊數(shù)據(jù)同時(shí)滿足約束規(guī)則

enable novalidate:舊數(shù)據(jù)可以不滿足,檢新插入的數(shù)據(jù)要滿足約束

disable validate:不允許在表上執(zhí)行任何DML操作,主要用在分區(qū)表,對(duì)于主鍵和唯一約事,會(huì)刪除相應(yīng)的唯一索引,但約束狀態(tài)任可用

disable novalidate數(shù)據(jù)可不滿足約束規(guī)則,對(duì)于主鍵和唯一約事,會(huì)刪除相應(yīng)的唯一索引,

約束常用語(yǔ)句

create table t(i number,v mubmer not null)  
create table t(i number,v mubmer unique)  
create table t(i number constraint pk_i primary key,v number)  
create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v));  
alter table t add constraint pk_i primary key (i)  
alter table t modify i not null;  
alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];  
alter table t add constraint t_i check(i in (1,2,3,4,5));  
alter table t disable novalidate constraint i  
alter table t enable novalidate constraint check_i  
alter table t drop constraint i;  
alter table t drop primary key i; 

#常用的數(shù)據(jù)字典

dba_constraints  
dba_cons_columns  
user_cons_columns  
user_constraints  

簡(jiǎn)單應(yīng)用

檢驗(yàn)當(dāng)為一個(gè)表建立主鍵索時(shí)后，這個(gè)字段是否滿足約束非空，唯一性，而且自動(dòng)建立一個(gè)索引，并查看當(dāng)把約束狀態(tài)關(guān)閉再次插入相同的記錄,是否還能把把約束設(shè)為enable ividate狀態(tài)。

SQL> create table t(i number constraint pk_i primary key,v number);  
SQL> insert into t values(1,2);  
SQL> insert into t values(3,4);  
SQL> commit;  
SQL> select * from t;  
         I          V  
---------- ---------------------------  
         1          2  
         3          4  

現(xiàn)在表中有兩條記錄，然后給它插主鍵為空或相同的值

SQL> insert into t values('',10);  
ERROR at line 1:  
ORA-01400: cannot insert NULL into ("Y"."T"."I")  
SQL> insert into t values(1,10);  
ERROR at line 1:  
ORA-00001: unique constraint (Y.PK_I) violated  

可以看到全部報(bào)錯(cuò)，此時(shí)主鍵不能為空或重復(fù)

查看是否建立索引

SQL> select index_name from user_indexes;  
INDEX_NAME  
------------------------------  
PK_I  

把約束關(guān)閉再次做同樣的操用

SQL> alter table t disable novalidate constraint pk_i;  
Table altered.  
SQL> insert into t values('',10);  
1 row created.  
SQL> insert into t values(1,10);  
1 row created.  
SQL> commit;  
Commit complete.  
SQL> select * from t;  
         I          V  
---------- ----------  
         1          2  
         3          4  
                   10  
         1         10  
SQL>  select index_name from user_indexes;  
no rows selected  

可見(jiàn)當(dāng)把約束關(guān)閉后就可以何意給表插數(shù)據(jù)了，而具索引也自動(dòng)刪除了。

現(xiàn)在激活約束

SQL> alter table t enable validate constraint pk_i;  
alter table t enable validate constraint pk_i  
ERROR at line 1:  
ORA-02437: cannot validate (SYS.PK_I) - primary key violated  

因?yàn)楸碇兄麈I有相同的值所以不能恢復(fù)到enable validate狀態(tài)了

再次測(cè)試回復(fù)到enable novalidate

SQL> alter table t enable novalidate constraint pk_i;  
alter table t enable validate constraint pk_i  
ERROR at line 1:  
ORA-02437: cannot validate (SYS.PK_I) - primary key violated  

也失敗了,

因?yàn)楸碇兄麈I有了空值和相同的值,所以恢復(fù)不到enable validate狀態(tài),但enable novalidate不檢查舊數(shù)據(jù)所以應(yīng)該還能恢復(fù)到enable novalidate.

要想恢復(fù)到enable novalidate必須建立主鍵索引(關(guān)閉約束時(shí)自動(dòng)刪除的那個(gè)索引)如下:

SQL> create index pk_i on t(i);  
Index created.  

然后恢復(fù)到enable disvalidate,以后再插數(shù)據(jù)不能為空,主鍵也不能重復(fù)了.

SQL> alter table t enable novalidate constraint pk_i;  
Table altered.  
SQL> insert into t values(1,14);  
insert into t values(1,14)  
ERROR at line 1:  
ORA-00001: unique constraint (SYS.PK_I) violated  

2.修正約束數(shù)據(jù)

當(dāng)給一個(gè)表作主鍵約束時(shí),因?yàn)橐汛鏀?shù)據(jù)不滿足約束規(guī)則,會(huì)提示錯(cuò)誤信息,些時(shí)必須對(duì)數(shù)據(jù)進(jìn)行修正

要修正數(shù)據(jù)先找出不滿足約束的數(shù)據(jù)

如下表,有不滿足約束的數(shù)據(jù)

SQL> select * from t;  
         I          V  
---------- ------------------------  
         1          2  
         3          4  
         15         12  
         15         10  

如果一個(gè)表數(shù)據(jù)量多可通過(guò)如下方法查找

SQL> alter table t drop constraint pk_i;    
Table altered.  
SQL>conn y / 123  
SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql  
Table created.  
SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;  
select * from t where rowid in (select row_id from exceptions)  
         I          V  
---------- ------------------------  
         15         12  
         15         10  

找到了重復(fù)的記錄

修正

SQL>update t set i=10 where v=12;  
SQL> select * from t;  
         I          V  
---------- ----------  
         1          2  
         3          4  
         10         12  
         15         10  

再建主鍵約束

alter table t add constraint pk_i primary key (i)  
Table altered.  

成功了!!!

#p#

二:分區(qū)表管理

作用:將在張大表的數(shù)據(jù)分布到多個(gè)表分區(qū)段,不同分區(qū)彼此獨(dú)立,從而提高了表的可用性和性能

種類(lèi):范圍分區(qū),散列分區(qū)(使用HASH算法,最常使用),列表分區(qū),范圍/散列組合分區(qū),范圍/列表組合分區(qū)

范圍分區(qū)表

創(chuàng)建范圍分區(qū)表

create table t(v number,b number)  
partition by range(v) (  
partition p1 values less than ('11') tablespace test1,  
partition p2 values less than ('21') tablespace test2);  

增加與刪除分區(qū)

#增加分區(qū)

alter table t add partition p3 values less than ('31') tablespace test3;  
alter table t drop partition p3  

一個(gè)時(shí)間分區(qū)的例子

alter session set nls_data_lanage=AMERICAN;  
alter session set nls_data_format='DD-MON-YYYY' 
create table t(v_date date,b number)  
partition by range(v_date)(  
partition p1 values less than ('01-APR-2009') tablespace test1,  
partition p2 values less than ('01-JUN-2009') tablespace test2);  

2.散列分區(qū)表(最常用)

創(chuàng)建

create table t1(  
v number,b number)  
partition by hash(v)  
(partition p1 tablespace test1,  
partition p2 tablespace test2);  

增加分區(qū)

alter table t add partition p3 tablespace test3;  

刪除分區(qū)

alter table t drop coalesce partition;  

3.列表分區(qū)

建列表分區(qū)

create table t(  
v varchar2(10),  
b number  
)partition by list(v)  
(partition p1 values('a','b') tablespace test1,  
partition p2 values('c','d') tablespace test2);  

#插入數(shù)據(jù)

SQL> insert into t values('a',10);  
SQL> insert into t values('d',20); 

#注意,插入數(shù)據(jù)時(shí)***個(gè)字段只能為a,b,c,d

SQL> insert into t values('f',30);  
ERROR at line 1:  
ORA-14400: inserted partition key does not map to any partition  

#查詢

select * from t;  
select * from t partition(p1);  
select * from t partition(p2);  
select * from t where v=XXX  

增加分區(qū)

alter table t add partition p3 values('31','32') tablespace test3;  

刪除分區(qū)

alter table t drop partition p3  

4.范圍/散列組合分區(qū)

建立散列組合分區(qū)

create table t(  
v number,b number)  
partition by range(v)  
subpartition by hash(b) subpartitions 2  
store in (test1,test2)(  
partition p1 values less than ('11'),  
partition p2 values less than ('21'));  

查詢

select * from t;  
select * from t partition(p1);  
select * from t where ....  

增加主分區(qū)和子分區(qū)

alter table t add partition p3 values less than ('31') tablespace test3;  
alter table t modify partition p3 add subpartition;  

刪除分區(qū)

alter table t coalesce partition;  
alter table t modify partition p1 coalesce subpartition;  

5.范圍/列表組合分區(qū)

創(chuàng)建

create table t(  
v number,b number)  
partition by range(v)  
subpartition by list(b)  
(partition p1 values less than ('11') tablespace test1(  
subpartition p1_1 values('1','3'),  
subpartition p1_2 values('5','6')  
),  
partition p2 values less than ('21') tablespace test2(  
subpartition p2_1 values('13','14'),  
subpartition p2_2 values('15','16')  
));  

查詢

select * from t  
select * from t partition(p1)  
select * from t subpartition(p1_1)  
select * from t where .....  
select segment_name,partition_name,tablespace_name  
from user_segments where segment_name='T';  

增加分區(qū)和子分區(qū)

alter table t add partition p3 values less than ('31') tablespace test3(  
subpartition p3_1 values('25','26'),  
subpartition p3_2 values('22','23'));  
alter table t modify partition r3  
add subpartition r3_3 tablespace test3 values('28','29');  

刪除分區(qū)

alter table t modify partition p1 coalesce subpartition;  

其它設(shè)置

交換分區(qū)數(shù)據(jù)  
alter table t exchange partition p1 with table tt;  
載斷分區(qū)  
alter table t truncate partition p1;  
修改分區(qū)名  
alter table t rename partition p2_1 to p2;  
合并分區(qū)  
alter table t merge partitions p1,p2 into partition p01  
重組分區(qū)  
alter table t move partition p1 tablespace test04  
為列表分區(qū)和子分區(qū)加值  
alter table t modify partition p1 add values('111');  
alter table t modify subpartition p3_1 add values('111');  
從列表分區(qū)和子分區(qū)中刪除值  
alter table t modify partition p1 drop values('111')  
alter table t modify subpartition p3_1 drop values('111')  

分區(qū)表常用的數(shù)據(jù)字典

分區(qū)表信息:  dba_part_tables  
顯示分區(qū):    dba_tab_partitions  
顯示子分區(qū):  dba_tab_subpartitions  
顯示分區(qū)列:  dba_part_key_columns  
顯示子分區(qū)列:dba_subpart_dey_columns  
顯示分區(qū)索引:dba_part_indexes  
顯示索引分區(qū):dba_ind_partitions  

來(lái)源：本文出自 “追求” 博客。

【編輯推薦】

1. Oracle數(shù)據(jù)庫(kù)中違反唯一約束的處理
2. Oracle與MySQL刪除字段時(shí)對(duì)索引與約束的處理對(duì)照
3. Oracle性能優(yōu)化借助分區(qū)技術(shù)實(shí)現(xiàn)
4. Oracle使用hash分區(qū)優(yōu)化分析函數(shù)查詢
5. Oracle數(shù)據(jù)庫(kù)文件恢復(fù)與備份思路