Oracle定義約束外鍵約束

外鍵約束保證參照完整性外鍵約束限定了一個列的取值范圍一個例子就是限定州名縮寫在一個有限值集合中這個值集合是另外一個控制結構——一張父表

　　下面我們創建一張參照表它提供了完整的州縮寫列表然後使用參照完整性確保學生們有正確的州縮寫第一張表是州參照表State作為主鍵

CREATE TABLE state_lookup
(state VARCHAR()
state_desc VARCHAR()) TABLESPACE student_data;
ALTER TABLE state_lookup
ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)
USING INDEX TABLESPACE student_index;

　　然後插入幾行記錄

INSERT INTO state_lookup VALUES (CA California);
INSERT INTO state_lookup VALUES (NY New York);
INSERT INTO state_lookup VALUES (NC North Carolina);

　　我們通過實現父子關系來保證參照完整性圖示如下

　　上圖顯示了State_Lookup表和Students表間一對多的關系State_Lookup表定義了州縮寫通用集合——在表中每一個州出現一次因此State_Lookup表的主鍵是State字段

　　State_Lookup表中的一個州名可以在Students表中出現多次有許多學生來自同一個州一次在表State_Lookup和Students之間參照完整性實現了一對多的關系

　　外鍵同時保證Students表中State字段的完整性每一個學生總是有個State_lookup表中成員的州縮寫

　　外鍵約束創建在子表下面在students表上創建一個外鍵約束State字段參照state_lookup表的主鍵

　　創建表

CREATE TABLE students
(student_id    VARCHAR() NOT NULL
student_name VARCHAR() NOT NULL
college_major VARCHAR() NOT NULL
status        VARCHAR() NOT NULL
state         VARCHAR()
license_no    VARCHAR()) TABLESPACE student_data;

　　創建主鍵

ALTER TABLE students
ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
USING INDEX TABLESPACE student_index;

　　創建Unique約束

ALTER TABLE students
ADD CONSTRAINT uk_students_license
UNIQUE (state license_no)
USING INDEX TABLESPACE student_index;

　　創建Check約束

ALTER TABLE students
ADD CONSTRAINT ck_students_st_lic
CHECK ((state IS NULL AND license_no IS NULL) OR
(state IS NOT NULL AND license_no is NOT NULL));

　　創建外鍵約束

ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);

　　一Errors的四種類型

　　參照完整性規則在父表更新刪除期間和子表插入更新期間強制執行被參照完整性影響的SQL語句是PARENTUPDATE 父表更新操作不能把State_lookup表中的state值更新為students表仍在使用而State_lookup表中卻沒有的值

　　PARENTDELETE 父表刪除操作不能刪除State_lookup表中的state值後導致students表仍在使用而state_lookup表中卻沒有這個值

　　CHILDINSERT 子表插入操作不能插入一個state_llokup表中沒有的state的值CHILDUPDATE 子表更新操作不能把state的值更新為state_lookup表中沒有的state的值

　　下面示例說明四種錯誤類型

　　測試表結構及測試數據如下

STATE_LOOKUP

State          State Description
CA             California
NY             New York
NC             North Carolina

STUDENTS

Student ID    Student Name    College Major    Status    State      License NO
A          John            Biology          Degree     NULL      NULL
A          Mary            Math/Science     Degree     NULL      NULL
A          Kathryn         History          Degree     CA        MV
A          Steven          Biology          Degree     NY        MV
A          William         English          Degree     NC        MV

　　）PARENTUPDATE

SQL> UPDATE state_lookup
SET state = XX
WHERE state = CA;
UPDATE state_lookup
*
ERROR at line :
ORA: integrity constraint (SCOTTFK_STUDENTS_STATE)
violated 每 child record found

　　）PARENTDELETE

SQL> DELETE FROM state_lookup
WHERE state = CA;
DELETE FROM state_lookup
*
ERROR at line :
ORA: integrity constraint (SCOTTFK_STUDENTS_STATE)
violated 每 child record found

　　）CHILDINSERT

SQL> INSERT INTO STUDENTS
VALUES (A
JosephHistoryDegreeXXMV);
INSERT INTO STUDENTS
*
ERROR at line :
ORA: integrity constraint (SCOTTFK_STUDENTS_STATE)
violated parent key not found

　　）CHILDUPDATE

SQL> UPDATE students
SET state = XX
WHERE student_id = A;
UPDATE students
*
ERROR at line :
ORA: integrity constraint (SCOTTFK_STUDENTS_STATE)
violated parent key not found

　　上面四種類型錯誤都有一個同樣的錯誤代碼ORA

　　參照完整性是數據庫設計的關鍵一部分一個既不是其他表的父表也不是子表的表是非常少的

　　二級聯刪除

　　外鍵語法有個選項可以指定級聯刪除特征這個特征僅作用於父表的刪除語句

　　使用這個選項父表的一個刪除操作將會自動刪除所有相關的子表記錄

　　使用創建外鍵約束的DELETE CASCADE選項然後跟著一條delete語句刪除state_lookup表中California的記錄及students表中所有有California執照的學生

ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state)
ON DELETE CASCADE;
執行刪除語句
DELETE FROM state_lookup WHERE state = CA;

　　然後再查詢students表中的數據就沒有了字段state值為CA的記錄了

　　如果表間有外鍵關聯但沒有使用級聯刪除選項那麼刪除操作將會失敗

　　定義一個級聯刪除時需要考慮下面問題

　　級聯刪除是否適合本應用？從一個父參照表的以外刪除不應該刪除客戶帳號

　　定義的鏈是什麼？查看表與其他表的關聯考慮潛在的影響和一次刪除的數量級及它會帶來什麼樣的影響

　　如果不能級聯刪除可設置子表外鍵字段值為null使用on delete set null語句（外鍵字段不能設置not null約束）

ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state)
ON DELETE SET NULL;

　　三參照字段語法結構

　　創建外鍵約束是外鍵字段參照父表的主鍵或Unique約束字段這種情況下可以不指定外鍵參照字段名如下ALTER TABLE students ADD CONSTRAINT fk_students_state FOREIGN KEY （state） REFERENCES state_lookup當沒有指定參照字段時默認參照字段是父表的主鍵

　　如果外鍵字段參照的是Unique而非Primary Key字段必須在add constraint語句中指定字段名

　　四不同用戶模式和數據庫實例間的參照完整性

　　外鍵約束可以在不同用戶模式及不同數據庫實例間應用但從一個數據庫管理觀點來說這是不希望的一個應用放在一個用戶模式下是比較合適的這裡不再詳述

From:http://tw.wingwit.com/Article/program/Oracle/201311/17654.html