Oracle定義約束外鍵約束

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

創新互聯2013年至今，是專業互聯網技術服務公司，擁有項目網站建設、做網站網站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元閬中做網站,已為上家服務,為閬中各地企業和個人服務,聯系電話:028-86922220

下面我們創建一張參照表 它提供了完整的州縮寫列表 然后使用參照完整性確保學生們有正確的州縮寫 第一張表是州參照表 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 );

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

外鍵字段存在于Students表中|State_lookup? |? 是State字段 ?? 一個外鍵必須參照主鍵或Unique字段?|?????????? 這個例子中 我們參照的是State字段?|?????????? 它是一個主鍵字段（參看DDL）?????????? /|\? |? Students??? |?

上圖顯示了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 studentsADD CONSTRAINT pk_students PRIMARY KEY (student_id)USING INDEX TABLESPACE student_index;

創建Unique約束

ALTER TABLE studentsADD CONSTRAINT uk_students_licenseUNIQUE (state license_no)USING INDEX TABLESPACE student_index;

創建Check約束

ALTER TABLE studentsADD CONSTRAINT ck_students_st_licCHECK ((state IS NULL AND license_no IS NULL) OR(state IS NOT NULL AND license_no is NOT NULL));

創建外鍵約束

ALTER TABLE studentsADD CONSTRAINT fk_students_stateFOREIGN KEY (state) REFERENCES state_lookup (state);

一 Errors的四種類型

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

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

CHILD INSERT??? 子表插入操作? 不能插入一個state_llokup表中沒有的state的值CHILD UPDATE??? 子表更新操作? 不能把state的值更新為state_lookup表中沒有的state的值

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

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

STATE_LOOKUP State????????? State DescriptionCA???????????? CaliforniaNY???????????? New YorkNC???????????? North Carolina STUDENTS Student ID??? Student Name??? College Major??? Status??? State????? License NOA ????????? John??????????? Biology????????? Degree???? NULL????? NULLA ????????? Mary??????????? Math/Science???? Degree???? NULL????? NULLA ????????? Kathryn???????? History????????? Degree???? CA??????? MV A ????????? Steven????????? Biology????????? Degree???? NY??????? MV A ????????? William???????? English????????? Degree???? NC??????? MV

） PARENT UPDATE

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

） PARENT DELETE

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

） CHILD INSERT

SQL INSERT INTO STUDENTS? ? VALUES ( A ? ? Joseph History Degree XX MV ); INSERT INTO STUDENTS*ERROR at line :ORA : integrity constraint (SCOTT FK_STUDENTS_STATE)violated parent key not found

） CHILD UPDATE

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

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

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

二 級聯刪除

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

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

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

ALTER TABLE studentsADD CONSTRAINT fk_students_stateFOREIGN 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 studentsADD CONSTRAINT fk_students_stateFOREIGN 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語句中指定字段名

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

lishixinzhi/Article/program/Oracle/201311/17654

Oracle外鍵與其主鍵的實際應用方案

以下的文章主要是對Oracle主鍵與Oracle外鍵的實際應用方案的介紹 此篇文章是我很然偶在一網站上發現的 如果你對Oracle主鍵與Oracle外鍵的實際應用很感興趣的話 以下的文章就會給你提供更詳細的相關方面的知識

CREATE TABLE SCOTT MID_A_TAB

( A VARCHAR ( BYTE)

B VARCHAR ( BYTE)

DETPNO VARCHAR ( BYTE)

)TABLESPACE USERS ;

CREATE TABLE SCOTT MID_B_TAB

( A VARCHAR ( BYTE)

B VARCHAR ( BYTE)

DEPTNO VARCHAR ( BYTE)

)TABLESPACE USERS ;

給MID_A_TAB表添加主鍵

alter table mid_a_tab add constraint a_pk primary key (detpno);

給MID_B_TAB表添加Oracle主鍵

alter table mid_b_tab add constraint b_pk primary key(a);

給子表MID_B_TAB添加Oracle外鍵 并且引用主表MID_A_TAB的DETPNO列 并通過on delete cascade指定引用行為是級聯刪除

alter table mid_b_tab add constraint b_fk foreign key

(deptno) references mid_a_tab (detpno) on delete cascade;

向這樣就創建了好子表和Oracle主表

向主表添加數據記錄

SQL insert into mid_a_tab(a b detpno) values( );

已創建 行

已用時間: : :

向子表添加數據

SQL insert into mid_b_tab(a b deptno) values( );

insert into mid_b_tab values( )

*

第 行出現錯誤:

ORA : 違反唯一約束條件 (SCOTT B_PK)

已用時間: : :

可見上面的異常信息 那時因為子表插入的deptno的值是 然而此時我們主表中

detpno列只有一條記錄那就是 所以當子表插入數據時 在父表中不能夠找到該引用

列的記錄 所以出現異常

但我們可以這樣對子表的數據的進行插入(即 在子表的deptno列插入null 因為我們在建表的時候

并沒有對該列進行not null的約束限制)

SQL insert into mid_b_tab(a b deptno) values( null);

已創建 行

已用時間: : :

現在如果我們把子表mid_b_tab中deptno列加上not null約束

SQL alter table mid_b_tab modify deptno not null;

alter table mid_b_tab modify deptno not null

*

第 行出現錯誤:

ORA : 無法啟用 (SCOTT ) 找到空值

已用時間: : :

上面又出現異常 這是因為現在mid_b_tab表中有了一條記錄 就是我們先前添加的

那條記錄

null

現在我們要把該表的deptno列進行not null約束限制 所以Oracle不讓我們這樣干

那我們就只有把該表給delete或truncate掉 然后在修改deptno列為非空

SQL delete from mid_b_tab;

已刪除 行

已用時間: : :

再次修改子表mid_b_tab表的deptno列為非空

SQL alter table mid_b_tab modify deptno not null;

表已更改

已用時間: : :

修改成功！

我們再次插入數據

insert into mid_b_tab(a b deptno) values( null);

試試

SQL insert into mid_b_tab(a b deptno) values( null);

insert into mid_b_tab(a b deptno) values( null)

*

第 行出現錯誤:

ORA : 無法將 NULL 插入 ( SCOTT MID_B_TAB DEPTNO )

已用時間: : :

看見現在Oracle不讓我們插入空值了

所以我們在創建子表的Oracle外鍵約束時 該表的引用列必須要進行not null限制 也可以在

該列創建unique 或primary key約束 并且引用列與被引用列的數據類型必須相同

SQL insert into mid_b_tab(a b deptno) values( );

已創建 行

已用時間: : :

此時數據插入成功 因為此時插入的 在主表中的被引用列中已經存在了

現在我們一系列的操作

SQL select * from mid_b_tab ;

A B DE

已用時間: : :

SQL select * from mid_a_tab;

A B DE

已用時間: : :

SQL delete from mid_a_tab;

已刪除 行

lishixinzhi/Article/program/Oracle/201311/18331

在oracle數據庫中怎樣創建主外鍵關系

主外鍵需要依托多表來創建，方法如下：

創建主表：

CREATE?TABLE?SCOTT.DEPT?

(DEPTNO?NUMBER(2,0)?PARIMARY?KEY,?

DNAME?VARCHAR2(14),?

LOC?VARCHAR2(13));

創建副表及外鍵：

CREATE?TABLE?EMP?

(EMPNO?NUMBER(4,0)?PRIMARY?KEY,?

ENAME?VARCHAR2(10),?

JOB?VARCHAR2(9),?

MGR?NUMBER(4,0),?

HIREDATE?DATE,?

SAL?NUMBER(7,2),?

COMM?NUMBER(7,2),?

DEPTNO?NUMBER(2,0),

CONSTRAINT?FK_DEPTNO?FOREIGN?KEY?(DEPTNO)?REFERENCES?DEPT?(DEPTNO));

新聞標題：oracle如何關聯外鍵 oracle 外鍵作用
文章網址：http://www.yijiale78.com/article10/hhipdo.html

成都網站建設公司_創新互聯，為您提供域名注冊、網站制作、網站營銷、全網營銷推廣、網站導航、企業建站

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯