Oracle資料庫的完整性條件約束規則

oracle|資料|資料庫
完整性條件約束用於增強資料的完整性，Oracle提供了5種完整性條件約束：

　　　 Check
　　　 NOT NULL
　　　 Unique
　　　 Primary
　　　 Foreign key

　　完整性條件約束是一種規則，不佔用任何資料庫空間。完整性條件約束存在資料字典中，在執行SQL或PL/SQL期間使用。使用者可以指明約束是啟用的還是禁用的，當約束啟用時，他增強了資料的完整性，否則，則反之，但約束始終存在於資料字典中。

　　禁用約束，使用ALTER語句

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
　　或

ALTER TABLE policies DISABLE CONSTRAINT chk_gender
　　如果要重新啟用約束：

ALTER TABLE policies ENABLE CONSTRAINT chk_gender
　　刪除約束

ALTER TABLE table_name DROP CONSTRAINT constraint_name
　　或

ALTER TABLE policies DROP CONSTRAINT chk_gender;
　　Check 約束

　　在資料列上Check 約束需要 一個特殊的布爾條件或者將資料列設定成TRUE，至少一個資料列的值是NULL，Check約束用於增強表中資料內容的簡單的商業規則。使用者使用Check約束保證資料規則的一致性。Check約束可以涉及該行同屬Check約束的其他資料列但不能涉及其他行或其他表，或調用函數SYSDATE,UID,USER,USERENV。如果使用者的商業規則需要這類的資料檢查，那麼可以使用觸發器。Check約束不保護LOB資料類型的資料列和對象、巢狀表格、VARRY、ref等。單一資料列可以有多個Check約束保護，一個Check約束可以保護多個資料列。

　　建立表的Check約束使用CREATE TABLE語句，更改表的約束使用ALTER TABLE語句。

　　文法：

CONSTRAINT [constraint_name] CHECK (condition);
　　Check約束可以被建立或增加為一個資料表條件約束，當Check約束保護多個資料列時，必須使用資料表條件約束文法。約束名是可選的並且如果這個名字不存在，那麼oracle將產生一個以SYS_開始的唯一的名字。

　　例：

CREATE TABLE policies
(policy_id NUMBER,
holder_name VARCHAR2(40),
gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'),
marital_status VARCHAR2(1),
date_of_birth DATE,
constraint chk_marital CHECK (marital_status in('S','M','D','W'))
);

　　NOT NULL約束

　　NOT NULL約束應用在單一的資料列上，並且他保護的資料列必須要有資料值。預設狀況下，ORACLE允許任何列都可以有NULL值。某些商業規則要求某資料列必須要有值，NOT NULL約束將確保該列的所有資料行都有值。

　　例：

CREATE TABLE policies
(policy_id NUMBER,
holder_name VARCHAR2(40) NOT NULL,
gender VARCHAR2(1),
marital_status VARCHAR2(1),
date_of_birth DATE NOT NULL
);
　　對於NOT NULL的ALTER TABLE語句與其他約束稍微有點不同。

ALTER TABLE policies MODIFY holder_name NOT NULL
唯一性限制式(Unique constraint)

　　唯一性限制式可以保護表中多個資料列，保證在保護的資料列中任何兩行的資料都不相同。唯一性限制式與表一起建立，在唯一性限制式建立後，可以使用ALTER TABLE語句修改。

　　文法：

column_name data_type CONSTRAINT constraint_name UNIQUE
　　如果唯一性限制式保護多個資料列，那麼唯一性限制式要作為資料表條件約束增加。文法如下：

CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)
　　唯一性限制式由一個B-tree索引增強，所以可以在USING子串中為索引使用特殊特徵，比如資料表空間或儲存參數。CREATE TABLE語句在建立唯一性限制式的同時也給目標資料列建立了一個唯一的索引。

CREATE TABLE insured_autos
(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
vin VARCHAR2(10),
coverage_begin DATE,
coverage_term NUMBER,
CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
);
　　使用者可以禁用未以性約束，但他仍然存在，禁用唯一性限制式使用ALTER TABLE 語句

ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;
　　刪除唯一性限制式，使用ALTER TABLE....DROP CONSTRAIN語句

ALTER TABLE insured_autos DROP CONSTRAIN unique_name;
　　注意使用者不能刪除在有外部鍵指向的表的唯一性限制式。這種情況下使用者必須首先禁用或刪除外部鍵（foreign key)。

　　刪除或禁用唯一性限制式通常同時刪除相關聯的唯一索引，因而降低了資料庫效能。經常刪除或禁用唯一性限制式有可能導致丟失索引帶來的效能錯誤。要避免這樣錯誤，可以採取下面的步驟：

　　1、在唯一性限制式保護的資料列上建立非唯一性索引。

　　2、添加唯一性限制式

　　主鍵(Primary Key)約束

　　表有唯一的主鍵約束。表的主鍵可以保護一個或多個列，主鍵約束可與NOT NULL約束共同作用於每一資料列。NOT NULL約束和唯一性限制式的組合將保證主鍵唯一地標識每一行。像唯一性限制式一樣，主鍵由B-tree索引增強。

　　建立主鍵約束使用CREATE TABLE語句與表一起建立，如果表已經建立了，可以使用ALTER TABLE語句。

CREATE TABLE policies
(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
holder_name VARCHAR2(40),
gender VARCHAR2(1),
marital_status VARCHAR2(1),
date_of_birth DATE
);
　　與唯一性限制式一樣，如果主鍵約束保護多個資料列，那麼必須作為一個資料表條件約束建立。

CREATE TABLE insured_autos
(policy_id NUMBER,
vin VARCHAR2(40),
coverage_begin DATE,
coverage_term NUMBER,
CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)
USING INDEX TABLESPACE index
STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
);
　　禁用或刪除主鍵必須與ALTER TABLE 語句一起使用

ALTER TABLE policies DROP PRIMARY KEY;
　　或

ALTER TABLE policies DISABLE PRIMARY KEY;
　　外部鍵約束（Foreign key constraint)

　　外部鍵約束保護一個或多個資料列，保證每個資料行的資料包含一個或多個null值，或者在保護的資料列上同時擁有主鍵約束或唯一性限制式。引用（主鍵或唯一性限制式）約束可以保護同一個表，也可以保護不同的表。與主鍵和唯一性限制式不同外部鍵不會隱式建立一個B-tree索引。在處理外部鍵時，我們常常使用術語父表（parent table)和子表（child table),父表表示被引用主鍵或唯一性限制式的表，子表表示引用主鍵和唯一性限制式的表。

　　建立外部鍵使用CREATE TABLE語句，如果表已經建立了，那麼使用ALTER TABLE語句。

CREATE TABLE insured_autos
(policy_id NUMBER CONSTRAINT policy_fk
REFERENCE policies(policy_id
ON DELETE CASCADE,
vin VARCHAR2(40),
coverage_begin DATE,
coverage_term NUMBER,
make VARCHAR2(30),
model VARCHAR(30),
year NUMBER,
CONSTRAIN auto_fk FROEIGN KEY (make,model,year)
REFERENCES automobiles (make,model,year)
ON DELETE SET NULL
);

　　ON DELETE子串告訴ORACLE如果父紀錄（parent record)被刪除後，子記錄做什麼。預設情況下禁止在子記錄還存在的情況下刪除父紀錄。

　　外部鍵和NULL值

　　在外部鍵約束保護的資料列中NULL值的處理可能產生不可預料的結果。ORACLE 使用ISO standar Match None規則增強外部鍵約束。這個規則規定如果任何外部鍵作用的資料列包含有一個NULL值，那麼任何保留該鍵的資料列在父表中沒有匹配值。

　　比如，在父表AUTOMOBILES中，主鍵作用於資料列MAKE，MODEL，YEAR上，使用者使用的表INSURED_AUTOS有一個外部約束指向AOTOMOBILES，注意在INSURES_AUTOS中有一資料行的MODEL列為NULL值，這一行資料已經通過約束檢查，即使MAKE列也沒有顯示在父表AUTOMOBILES中，如下表：

　　表1 AUTOMOBILES

MAKE MODEL YEARFord Taurus2000ToyotaCamry 1999
　　表2 INSURED_AUTOS

POLICY_IDMAKE MODEL YEAR576 Ford Taurus 2000577 ToyotaCamry 1999 578 Tucker NULL 1949
　　延遲約束檢驗（Deferred Constraint Checking）

　　約束檢驗分兩種情況，一種是在每一條語句結束後檢驗資料是否滿足約束條件，這種檢驗稱為立即約束檢驗（immediately checking）,另一種是在交易處理完成之後對資料進行檢驗稱之為延遲約束檢驗。在預設情況下Oracle約束檢驗是立即檢驗（immediately checking),如果不滿足約束將先是一條錯誤資訊，但使用者可以通過SET CONSTRAINT語句選擇延遲約束檢驗。文法如下：

SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;
序列（Sequences）

　　Oracle序列是一個連續的數字產生器。序列常用於人為的關鍵字，或給資料行排序否則資料行是無序的。像約束一樣，序列只存在於資料字典中。序號可以被設定為上升、下降，可以沒有限制或重複使用直到一個限制值。建立序列使用SET SEQUENCE語句。

CREATE SEQUENCE [schema] sequence KEYWORD
　　KEYWORD包括下面的值：


KEYWORD 描述START WITH 定義序列產生的第一個數字，預設為1INCREMENT BY 定義序號是上升還是下降，對於一個降序的序列INCREMENT BY為負值MINVALUE 定義序列可以產生的最小值，這是降序序列中的限制值。預設情況下該值為NOMINVALUE,NOMINVALUE，對於升序為1，對於降序為-10E26.MAXVALUE 序列能產生的最大數字。這是升序序列中的限制值，預設的MAXVALUE為NOMAXVALUE,NOMAXVALUE，對於升序為10E26，對於降序為-1。CYCLE 設定序列值在達到限制值以後可以重複NOCYCLE 設定序列值在達到限制值以後不能重複，這是預設設定。當試圖產生MAXVALUE+1的值時，將會產生一個異常CACHE 定義序列值佔據的記憶體塊的大小，預設值為20NOCACHE 在每次序號產生時強制資料字典更新，保證在序列值之間沒有間隔當建立序列時，START WITH值必須等於或大於MINVALUE。
　　刪除序列使用DROP SEQUENCE語句

DROP SEQUENCE sequence_name

　　索引(INDEXES)

　　索引是一種可以提高查詢效能的資料結構，在這一部分我們將討論索引如何提高查詢效能的。ORACLE提供了以下幾種索引：

　　 B-Tree、雜湊（hash)、位元影像（bitmap)等索引類型
　　 基於原始表的索引
　　 基於函數的索引
　　 域（Domain）索引

　　實際應用中主要是B-Tree索引和位元影像索引，所以我們將集中討論這兩種索引類型。

　　B-Tree索引

　　B-Tree索引是最普通的索引，預設條件下建立的索引就是這種類型的索引。B-Tree索引可以是唯一或非唯一的，可以是單一的（基於一列）或串連的（多列）。B-Tree索引在檢索高基數資料列（高基數資料列是指該列有很多不同的值）時提供了最好的效能。對於取出較小的資料B-Tree索引比全表檢索提供了更有效方法。但當檢查的範圍超過表的10%時就不能提高取回資料的效能。正如名字所暗示的那樣，B-Tree索引是基於二元樹的，由枝幹塊（branch block)和樹葉塊（leaf block)組成,枝幹塊包含了索引列（關鍵字）和另一索引的地址。樹葉塊包含了關鍵字和給表中每個匹配行的ROWID。

　　位元影像索引

　　位元影像索引主要用於決策支援系統或待用資料，不支援行級鎖定。位元影像索引可以是簡單的（單列）也可以是串連的（多列），但在實踐中絕大多數是簡單的。位元影像索引最好用於低到中群集（cardinality)列，在這些列上多位元影像索引可以與AND或OR操作符結合使用。位元影像索引使用位元影像作為索引值，對於表中的每一資料行位元影像包含了TRUE（1）、FALSE（0）、或NULL值。位元影像索引的位元影像存放在B-Tree結構的頁節點中。B-Tree結構使尋找位元影像非常方便和快速。另外，位元影像以一種壓縮格式存放，因此佔用的磁碟空間比B-Tree索引要小得多。

　　同義字（Synonyms）

　　對另一個資料對象而言同義字是一個別名。public同義字是針對所有使用者的，相對而言private同義字則只針對對象擁有者或被授予許可權的賬戶。在本機資料庫中同義字可以表示表、視圖、序列、程式、函數或包等資料對象，也可以通過連結資料表示另一個資料庫的對象。

　　建立同義字文法如下：

CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];　　
　　例：

CREATE PUBLIC SYNONYM policies FOR poladm.policies@prod;

CREATE SYNONYM plan_table FOR system.plan_table;